Date Query

[Guest]

I would like to create a filter in my query that returns all days in a month
from 2 months ago.

Example. it is July so I want to return all values with any date in May
only. In August I want to return all values for the month of june only. In
September I want to return the values from July only etc...

 

[John Spencer]

Assumption:
Your date field contains just the date and no associated time.

Under your date field use criteria like

Between DateSerial(Year(Date()),Month(Date())-2,1) and
DateSerial(Year(Date()),Month(Date())-1,0)

If your date field also contains a time (other than midnight)
YourDateField >= DateSerial(Year(Date()),Month(Date())-2,1) and
YourDateField <DateSerial(Year(Date()),Month(Date())-1,1)

 

[Guest]

Thanks for the help

can you explain the syntax of that statement for my future use.

I understand dateserial(year,month,day)

Does the last zero represent that last day of any month?

 

[John Spencer]

The Zero Day of any month is the last day of the prior month. Another way
to look at it is

DateSerial(Year, Month, 1) -1

Or
DateSerial(Year, Month, 1-1)

The dateserial function is good about adjusting when you put in negative
numbers, etc.
DateSerial(2006,1-12,1) Will return 1-1-2005 the same as
DateSerial(2006,-11,1)

If you put in a year number that is 1 or 2 digits, then DateSerial uses the
year cutoff to calculate the century.