Select Query for the last three months

[MTBer]

I have a date field in my access table and I'm only intersted in the data if the date falls within the last three months, based on the Latest date in the table,
ie If the latest date in the data is 30th April 2004, i want to return the data >= 01/02/2004.

how do i enter this into the criteria field. I had been using a parameter popup box, but I need to make it a little more automated.

 

[Philem]

Hullo. Try putting

>Date()-90

in the criteria for date in your query.

HTH

P

 

[Joe4]

based on the Latest date in the table

If you want to go back 3 months from the latest date in the table, the only solution I can think of involves VBA and SQL. Basically, you have to loop through the RecordSet (your table), find the maximum value, then build your query & criteria in an SQL statement.

I don't know if you are interested in a solution that complicated. Maybe someone else knows an easier way, but I don't know of one.

 

[Norie]

Try a look at the DateDiff function

 

[Joe4]

Norie,

Now you have me interested...

How would you propose using it in this situation? Remember, you need to first identify the latest Date in the table before establishing the 3 month criteria (it is not from the current date).

 

[MTBer]

well I got out my sql book, and had a play and this works for me now;

SELECT tblSales.Date, tblSales.Sales
FROM tblSales
WHERE tblSales.Date >= (SELECT (Max(tblSales.Date)-63)-day(Max(tblSales.Date)-63)+1 AS MaxOfDate
FROM tblSales);

I found that if the last date was 20 october 2004, i need the records for the last three months, starting on the first of the month, therefore august, september, october.

I take 63 from the latest date, the second part establishes the day value of the date i know have ie 22 and subtractes this from the date, and finally it adds 1, to give the date.

I editted the code to subtract 63, because if the last date is 31/08/2004 taking 60 from this date gives me 02/07/2004 so 63 should allow me a margin of error when two months have 31 days.

Its easier in excel