List records with recent month and previous 2

[Guest]

I would like to create a query that would detect the most recent month and
would list those records as well as records with dates from the previous 2
months. All dates are end-of-month values. 01/31/07, 12/31/06, 11/30/06,
10/31/06 etc.

 

[Guest]

Put something like the following in the criteria of the date field. Replace
both "ex_date" with the field name and "tblDates" with the table name, You
might also need to change the -2 to -3.

Between DMax("ex_date","tblDates") And
DateAdd("m",-2,DMax("ex_date","tblDates"))

If you have a lot of records, like in the tens of thousands, the DMax
function could be rather slow. I would help if your date field is indexed.

 

[John Spencer]

Field: YourDateField
Criteria: Between DateSerial(Year(Date()),Month(Date())-2,0) And
DateSerial(Year(Date()),Month(Date())+1,0)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michael Gramelspacher]

(e-mail address removed)>, (e-mail address removed)
says...

I would like to create a query that would detect the most recent month and
would list those records as well as records with dates from the previous 2
months. All dates are end-of-month values. 01/31/07, 12/31/06, 11/30/06,
10/31/06 etc.

maybe something like this would also work (Northwind Query):

PARAMETERS [Enter Date:] DateTime;
SELECT *
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID=
[Order Subtotals].OrderID
WHERE Orders.ShippedDate Is Not Null And datediff("m",
0,Orders.ShippedDate) between DATEDIFF("m",0,[Enter Date:])-2
And DATEDIFF("m",0,[Enter Date:])
ORDER BY Orders.ShippedDate;