Search for overdue payments

[John Hipskind]

I would like create a macro that finds all overdue
payments.
When my Accounts Receivable report is opened, I would
like an option to search for (and print) all payments
that are 30 days older than today's date.
Can I use ApplyFilter in a macro assigned to an OnOpen
report event? If so, how do I set a Where Condition that
tests whether today's date is 30 days later than the
invoice's sent date? I would also like to print out the
number of late days in a field on the report.
Thanks.
John

 

[Ken Snell]

Comments inline....


--
Ken Snell
<MS ACCESS MVP>

I would like create a macro that finds all overdue
payments.
When my Accounts Receivable report is opened, I would
like an option to search for (and print) all payments
that are 30 days older than today's date.
Can I use ApplyFilter in a macro assigned to an OnOpen
report event?

Yes, but I would use the "WHERE" argument of the OpenReport action instead.

MacroName
Action: OpenReport
Where: [SentDateField] < Date() - 30


If so, how do I set a Where Condition that

tests whether today's date is 30 days later than the
invoice's sent date?

See answer above.

I would also like to print out the
number of late days in a field on the report.

Your report's recordsource query will need a calculated field that does this
calculation.

DaysLate: DateDiff("d", [SentDateField], Date())

In this situation, you can replace the WHERE statement above with this:

[DaysLate] > 30