Queries Totalling Balances Based on Time/Date

[Zacko]

I am making a report that shows customer invoice details. It is based on two queries joined by the key field Account ID.

Report
..... Account ID
..... Statement Date

Subreport
...... Account ID
..... Invoice Number
..... Invoice Date
..... Due Date
..... Invoice Total
..... Amount Paid
..... Outstanding Balance

There's a 30 day time frame between the Invoice Date and Due Date for each invoice.

I'd like to have three (3) fields at the bottom of the report that would...

1) Contain the sum of Oustanding Balance for invoices past their Due Date.

Maybe a logic like this:

If Due Date < Statement Date
Then Sum([Outstanding Balance])

Call it OVER30

2) contain the sum of Outstanding Balance for all invoices issued in the current month.

Call it CURRENT

3) contain the sum of OVER30 and CURRENT, which would most likely be simply adding the two previous fields after they have been created.

Call it TOTAL


I'd really appreciate any help on how to create these three fields.

[Narender Sagar]

This is easy to do.
You can create a status column in the query using if statement, you can get three different status. Now in Report design view, create a group based on this logical column (you've created in the query). Now you can get Totals, based on this status column.
Pl. let me know, if you need some more details.