By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,635 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Forecast Report

P: n/a
I sorta understand where this approach is supposed to be going, but I
can't seem to make it work... The function in the query -
ExpectedGroup: ReturnGroup(ExpectedDt) - ExpectedDt here is the name
of my calculated field?

In the module, is datExpected supposed to also match my field name?

This runs with no errors, but gives me group one for all expected
dates. What am I doing wrong?

Thanks In Advance - Karen


View Message Thread (1 reply)

Results 1 - 2 of 2

Forcast Report
From: Karen
Date Posted: 2/11/2004 1:23:00 PM

I have created a query that calculates the expected payment date of
receivables based on an average of past payment.

I would like to run a report from this information that lists the
receiveables by Expected Date in coming 10 days, 30 days, etc.

How do I set a range for this? Is it done in the query or the report?
Re: Forcast Report
From: Salad
Date Posted: 2/12/2004 11:03:00 AM

Karen wrote:
I have created a query that calculates the expected payment date of
receivables based on an average of past payment.

I would like to run a report from this information that lists the
receiveables by Expected Date in coming 10 days, 30 days, etc.

How do I set a range for this? Is it done in the query or the report?


Somehow you need to set a grouping to your query. This is one method,
you
can figure out alternative methods. Let's assume the query for the
report
is called Expected. Create a new query, drop in Expected as the
query/table. Add a new column. Call it ExpectedGroup that calls a
function ReturnGroup. Ex:
ExpectedGroup : ReturnGroup(ExpectedDt)

In a module, create the function
Public Function ReturnGroup(datExpected As Date) As Integer
Select Case DateDiff("d",Date(),datExpected)
Case Is <= 10
ReturnGroup = 1
Case Is <= 30
ReturnGroup = 2
Case Else
ReturnGroup = 3
End Select
End Function

Now you can break and sort and filter on the group numbers..
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Karen wrote:
I sorta understand where this approach is supposed to be going, but I
can't seem to make it work... The function in the query -
ExpectedGroup: ReturnGroup(ExpectedDt) - ExpectedDt here is the name
of my calculated field?
Yes. If you are calling it in the query that calcs the date, pass the
calculation. A method that works just as well is to create a new query,
drag all the fields from the query that calcs the date and then add the
field ExpectedGroup: ReturnGroup(ExpectedDt) to this new query.
In the module, is datExpected supposed to also match my field name?
No. However, if you want to modify the code you can.

I'll assume your calculated date field is called ExpectedDt If not change
it.. If so, then that date of ExpectedDt is passed to the function. If
all of the expected dates are less than the current date you will get
negative numbers and all would be group 1.

Ex: ? DateDiff("d",Date(),Date()+1) will echo 1. If
DateDiff("d",Date()-1,Date()) it echoes -1.

This runs with no errors, but gives me group one for all expected
dates. What am I doing wrong?


Chances are, nothing.

If you reply, I'd recomment you reply to this thread, not create a new one.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.