create an aging report

[MLHab306]

Hi

I am a lightweight when it comes to access. I have no clue about code. I am trying to create an aging report (accounts recievable) past due 1 to 15 days, 16 to 30 days, 31 to 60 days etc. I have tried variations of date(), date()<15, etc. I would greatly appreciate any help.

Thank you in advance for your time and suggestions.

 

[Shirley]

Try this DatePart(&quot;dd&quot;, Date()) <15

 

[MLHab306]

Hi Shirley

I tried your suggestion and i recieved &quot;invalid procedure call&quot; error. I am trying different variations in my query.

I appreciate any and all suggestions.

Mark

 

[GingerR]

is the name of your field 'date'? if so you should change it because that's an Access keyword that means today/current date.

anyhow, assuming your field is called DueDate, this will tell you how many days DueDate is from TODAY:

Diff: DateDiff("d",[DueDate],Date())

so you can put that into a field in the query.

 

[dgillz]

Shirley's answer won't work, It will return everything with a date of less that 15, regardless of age. I do not have an answer, but I am very interested in what the solution would be.

In crystal reports there are funtions called Current, 31to60days, 61to90days, and over90days. If similar functions were put into modules I would guess that would work. The way they are used in crytal reports are as follows:

if {duedate} in 31to60Days then {amount} else 0

Food for thought
Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine
Cell/VM: 714-348-0964

 

[MLHab306]

Hi

After going thru a lot of posts I found a solution.
it is:

Between Date() And (Date()-30) (in first query)
Between Date()-31 And (Date()-60) (in second query) etc
then on my report I used subforms. Not exactly the way I wanted but it is a good start.

Thanks for your suggestions.

 

[GingerR]

in addition (instead of subreports) in one single query you can always put this to generate a STATUS, then sort a report based on the STATUS:

STATUS: iif(DueDate between Date()-1 and Date()-15,&quot;1-15 Days&quot;,iif(DueDate between Date()-16 and Date()-30,&quot;16-30 Days&quot;,iif(DueDate between Date()-31 and Date()-60,&quot;31-60 Days&quot;,&quot;Over 60 Days&quot))

then in your report you just base it on this query alone, and in SortingAndGrouping group by STATUS and make a header for it.

g