Aging Report

[tbrowne]

I'm designing a database that needs to print an aging report for invoices. I have never before worked with aging reports and was looking for some tips on report design. Possibly a small sample of an aging report. I haven't seen an aging report in any of the MS Sample databases.

Thanks!

 

[JoeMiller]

I assume you mean you want a column for 1-10, 11-30, 31-60?? If so you just need to use an iif statement that inspects the date of the document when displaying it. You'll use DateDiff to determine the difference in days then either show the value or not depending on if it falls in the correct range of days. Here's a sample control source for each control under the aged column looking at the DocumentDate field compared to today's date and showing the document balance if the date is in the correct range.

1-10 days
=IIf(DateDiff(&quot;d&quot;,[DocumentDate],Date())=<10,[DocBalance],Null)

11-30 days
=IIf(DateDiff(&quot;d&quot;,[DocumentDate],Date())>10 and DateDiff(&quot;d&quot;,[DocumentDate],Date())=<30,[DocBalance],Null)

HTH Joe Miller
joe.miller@flotech.net

 

[padinka]

Here is a copy of one of mine that computes 30-60-90-120 and 180 days past due.


SELECT dbo_tempCases.Inbox, Sum(IIf(DateDiff(&quot;d&quot;,[DateCreated],Now()) Between 30 And 59,1,0)) AS [30 Days], Sum(IIf(DateDiff(&quot;d&quot;,[DateCreated],Now()) Between 60 And 89,1,0)) AS [60 Days], Sum(IIf(DateDiff(&quot;d&quot;,[DateCreated],Now()) Between 90 And 119,1,0)) AS [90 Days], Sum(IIf(DateDiff(&quot;d&quot;,[DateCreated],Now()) Between 120 And 179,1,0)) AS [120 Days], Sum(IIf(DateDiff(&quot;d&quot;,[DateCreated],Now())>=180,1,0)) AS [180 Days]
FROM dbo_tempCases
GROUP BY dbo_tempCases.Inbox;

Trisha
padinka@yahoo.com

 

[padinka]

Forgot to mention this just give a count. But you can see the logic that Joe Miller has explained...
Trisha
padinka@yahoo.com