SUB REPORTS ? VBA ? (1 Viewer)

[Gabriel]

Hello friends

Application:
I have a report that prints statements for business accounts that have invoices that are past 45 day over due. After printing the report, a sub report that is in the parent report’s footer is printed. This sub report has a condensed list of all the accounts and their open invoices along with phone numbers. The idea is that after the statements are mailed we use our calling list and call accounts with invoices that are 75 days past due to let them know we are aware of their delinquency and to ask them to watch the mail for our statement.

Problem:
I don’t want statements to be printed for all accounts (that would be a huge printing job).
I only want a statement printed if the account has any invoices that are past 45 days. HOWEVER, I want all invoices for that account to show up on the statement for that account regardless of their date. Often an account will pay off of the statement, so while they have the checkbook in hand we want them to pay for all invoices NOT just those that are overdue. We have the same problem with the calling list. Not all accounts that have open invoices need to be called (that would unnecessarily run up the phone bill). In the sub report we need only those accounts that have invoices past 75 days. Here also while we have them on the phone we want to let them know of all invoices regardless of date.

Possible Solutions: ?
Each report is based on a different query. I can’t change the criteria [(<=Date()-65) or (<=Date()-65)] on the invoice date or things will get mixed up. Does the problem have to do with linking the reports? I don’t think so but I could be wrong. I think the answer is in vba. Please let me know what you think.

I’m posting this in the reports and vba areas.

Thanks – Gabriel

Tables:
Accounts
Orders

Queries:
Statements
StatementsSub

Reports:
Statements
StatementsSub

Fields:
AccountID
OrderID
OrDate

 

[Pat Hartman]

I would create a second query. The new query would have the selection criteria, ie. all accounts more than 75 days past due but select only the distinct customer number.
Select Distinct CustNum
From YourTable
Where InvoicePaidDt Is Null and InvoicedDate < Date() - 75;

Then add this query into your existing query and remove the date selection criteria from it but leave the open invoice criteria. This change will cause the original query to pull the past due invoice plus any other open invoices for the same customer.

 

[Richie]

Since your main report is based on query returning records over 45 days old create a similar query with records greater than 65 days old. I assume you group by customerID so drag the second query into the foot of the detail section add a sum to the group footer if you wish, the original invoice should print the invoices that are 45 days old and should also add the records that are over 65 days in the sub report it should not show any over 65 if they don't exist. Link the sub report by customerID