I want to run a report based on the query below. It is basically going to be an aging report on outstanding invoices. It will tell me how many days has passed since the invoice date. What I want the report to display is this:
InvoiceID ---- InvoiceDate --- 1-30 Days --- 31-60 Days --- 61-90 Days
1 ------------ 07/01/05 --------- $10.00
2 ------------ 08/02/05 -------------------------- $15.00
3 ------------ 02/05/05 ------------------------------------------ $20.00
And so on. Is there a way that I can display the amount according to the number of days they are outstanding? If not like this, is there another way of showing the "category" of days passed? (I can have Current, 1-30, 31-60, 61-90, and Over 90 categories).
I am using the following query that shows the amount of workdays between now and the invoice date. I want to use this number to tell it where it is designated on the report:
SELECT tblInvoicing.InvoiceID, tblInvoicing.InvoiceDate, Date() AS Today, (DateDiff("d", [InvoiceDate],[Today])-DateDiff("ww",[InvoiceDate],[Today],7)-DateDiff("ww",[InvoiceDate],[Today],1)) AS Status FROM tblInvoicing;
InvoiceID ---- InvoiceDate --- 1-30 Days --- 31-60 Days --- 61-90 Days
1 ------------ 07/01/05 --------- $10.00
2 ------------ 08/02/05 -------------------------- $15.00
3 ------------ 02/05/05 ------------------------------------------ $20.00
And so on. Is there a way that I can display the amount according to the number of days they are outstanding? If not like this, is there another way of showing the "category" of days passed? (I can have Current, 1-30, 31-60, 61-90, and Over 90 categories).
I am using the following query that shows the amount of workdays between now and the invoice date. I want to use this number to tell it where it is designated on the report:
SELECT tblInvoicing.InvoiceID, tblInvoicing.InvoiceDate, Date() AS Today, (DateDiff("d", [InvoiceDate],[Today])-DateDiff("ww",[InvoiceDate],[Today],7)-DateDiff("ww",[InvoiceDate],[Today],1)) AS Status FROM tblInvoicing;
