Debtor Report Formulation 1

[Russie]

Hi.

I have the following fields in a query and want to make a debtor report from them:

Amount
Account
Date
Event

What I need to do is to 'phase' this report, i.e.

Date Account 30 Days 60 Days 90 Days

01/04 000001 100
01/03 000002 100
01/02 000003 100

I need to calculate the age of the debt ( today()-Date ) and put the amount in the correct column...amounts of 30 days or less go in column 1, amounts of 60-30 days go in column 2 etc.

I'll give a star to the first correct answer.

Thanks for any help.

Russie

 

[Norris68]

You'll need to use the IIF function within your query, and it won't make for pretty reading.

SELECT [Date], Account, IIF(DateDiff(&quot;d&quot;,[Date],Date())<=30,Amount,0) AS 30Days, IIF(DateDiff(&quot;d&quot;,[Date],Date())>30 AND DateDiff(&quot;d&quot;,[Date],Date())<=60,Amount,0) AS 60Days FROM ...

NB: Because you have a field called Date you must enclose it within square brackets in your query to distinguish it from the built-in function Date() - both are used in the above example.

Another note: This query will return one row for each payment date. It will not aggregate them. You can, however, then create another query using this one as the source e.g. SELECT ... FROM qryDebtor GROUP BY Account