Search for overdue payments

  • Thread starter Thread starter John Hipskind
  • Start date Start date
J

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
 
Comments inline....


--
Ken Snell
<MS ACCESS MVP>

John Hipskind said:
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?
Click to expand...

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?
Click to expand...

See answer above.
I would also like to print out the
number of late days in a field on the report.
Click to expand...

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
 
Back
Top