Query to Look for all Records from Three Months Ago

[DFeil]

Hello,

I am trying to design a query that will show all records in a single month from 3 months prior to the current date. I tried the following expression >=DateAdd("m",-3,Date())
but that shows all the records from all three months prior. Anyone have a thought?


Thanks in advance Dave

 

[AccessMSSQL]

Try to use a between statement

between dateadd("m",-3,date()) and dateadd("M",-2,date())

OR
>= dateadd("m",-3,date()) and date <= dateadd("m",-2,date())

 

[DFeil]

Thanks for your response, tried both expressions, neither pulled the data. The field I am pulling the date from is named DateofHire, will that make any difference?

Dave

 

[AccessMSSQL]

Are you trying to use this in a query? Please post the syntax.

 

[AccessMSSQL]

your where clause needs to be something like this:
WHERE DateOfHire Between dateadd("m",-3,date()) and dateadd("m",-2,date())

 

[DFeil]

The query contains three fields pulled from a main table, the fields are FirstName, LastName and DateofHire. I have put the expression in the criteria section of the DateofHire field. I am trying to create a query that will pull all the records from the month three months prior to the current date, whatever that turns out to be. For example from todays date, I would need it to pull all the records from August 2012. Does this help.

 

[DFeil]

Thanks for your help, quitting till Monday, have a good weekend.

Dave

 

[AccessMSSQL]

Yes it does. Got it. You want the date range to start on the first of the month 3 months prior. So today would return all records between 8/1/2012 and 8/31/2012, correct?

 

[DFeil]

Yes that is exactly correct. Thank you.

 

[AccessMSSQL]

Here ya go - tested and it works:

Between DateSerial(Year(DateAdd("m",-3,Date())),Month(DateAdd("m",-3,Date())),1) And DateSerial(Year(DateAdd("m",-3,Date())),Month(DateAdd("m",-3,Date()))+1,0)

 

[DFeil]

YOU ROCK!!!!!!!!!!! It works! Thank you, would this expression work for years as well?

 

[jdraw]

There are Date related examples at techonthenet

just google "techonthenet access functions date" and you will get lots of info and examples.
The example will work for Years if adjusted properly -- as you will see at techonthenet.

Good luck.

 

[nanscombe]

Query column - monthOfHire:Format([DateofHire],"yyyymm")

Criteria - Format(dateadd("m",-3,date()),"yyyymm")