Query to Look for all Records for a single month from Three Months Ago

[DFeil]

The query contains three fields pulled from a main table, the fields are FirstName, LastName and DateofHire. I am looking for an 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. Thanks in advance for your help.



Dave

[pbaldy]

See if these get you started, using the Date() function for the date input (dte):

'First of previous month
myDate = DateSerial(Year(dte), Month(dte) - 1, 1)

'Last of previous month
myDate = DateSerial(Year(dte), Month(dte), 0)

[DFeil]

Tried that, no luck...

[pbaldy]

What exactly did you try, since an adaptation of those would certainly work?

[DFeil]

I tried pasting the expression (myDate = DateSerial(Year(dte), Month(dte) - 1, 1)) into the criteria field, then ran the query, no joy.

[pbaldy]

That was just an example of the useage. You'd just want the formula, and you'd use it in a criteria with Between:

Between FormulaForFirstOfMonth And FormulaForLastOfMonth

And like I said, you'd use the Date() function in place of dte.

[DFeil]

You mean like this...
between dateadd("m",-3,date()) and dateadd("M",-2,date())

[pbaldy]

I suspect that would run without error, but not return what you asked for in post 1 (but it's on the right track).

[DFeil]

It doesn't return anything...

[DFeil]

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

Dave

[pbaldy]

Can you post the db?