Filter Date field in a Query

[David]

Hi,

I have a table with a date field for each transaction- I want to be able to
filter the date so that it shows only records for 3 full months previous and
current month transactions- e.g running it today I want it to go back to
01/03/2009- so if I ran it next week it would still go back to 01/03/2009-
any idea what code I would put in the criteria section of the query?

 

[Klatuu]

WHERE Month(MyDateField) BETWEEN Month(MyDateField) -3 AND Month(MyDateField)

 

[Dirk Goldgar]

Hi,

I have a table with a date field for each transaction- I want to be able
to
filter the date so that it shows only records for 3 full months previous
and
current month transactions- e.g running it today I want it to go back to
01/03/2009- so if I ran it next week it would still go back to 01/03/2009-
any idea what code I would put in the criteria section of the query?

Here's one version:

SELECT ... FROM ...
WHERE [DateField] >= DateSerial(Year(Date()), Month(Date()) - 3, 1)
AND [DateField] < DateSerial(Year(Date()), Month(Date()) + 1, 1)

If there will be no transactions dated after the current date, you can
simplify that to:

SELECT ... FROM ...
WHERE [DateField] >= DateSerial(Year(Date()), Month(Date()) - 3, 1)