show customers who have not purchased product for "n" months

[TimTDP]

Sorry but I have got myself in a knot!:banghead:

I have the table tblInvoice which contains the fields CustomerId and InvoiceDate

I want to create a query that returns all customers who have not been invoiced in the last 3 months.
I have tried:

SELECT tblInvoice.CustomerId, Last(tblInvoice.InvoiceDate) AS LastOfInvoiceDate
FROM tblInvoice
GROUP BY tblInvoice.CustomerId
HAVING (((Last(tblInvoice.InvoiceDate))<=DateAdd("m",3,Date())))
ORDER BY Last(tblInvoice.InvoiceDate) DESC;

but this returns records for the current month which is incorrect!

How do I do this?

 

[TimTDP]

FOUND IT!
DateAdd("m",3,Date()) should be DateAdd("m",-3,Date())

 

[Brianwarnock]

You should use Max not Last to find the last date, Last only works by coincidence.

From Help

You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions

My bold

thus if you go back into the data base to change or add records you may/will not get what you expect.

Brian