Criteria Current Month

[Zaxxon]

Hi I have number of queries and I want them too all return data based from the current month. At the moment in the query I have [Date] Where Criteria Between #01/10/2008# And #31/10/2008#

Obviously I don't want to have to change it every month. Is there a way to make it select the current month? I was thinking something like =DatePart(m,Date()) but that doesn't seem to work.

I had looked into using a sort of date selector on a form: http://allenbrowne.com/casu-08.html
But the trouble I ran into was my different queries rely on different date fields.

Ultimately, I have a report for each query then a master report with all those reports as subreports.

 

[pbaldy]

You can use Between and formulas to get the first and last days of the current month:

http://www.pacificdb.com.au/Support/code_datetime.htm

or you could use

WHERE Month(DateField) = Month(Date()) AND Year(DateField) = Year(Date())

 

[Zaxxon]

The WHERE statement worked great. Thanks for your quick response.

I'll check that website out too, looks like a good resource.

 

[Zaxxon]

Just had some trouble entering the new year, so in case anyone had the same problem I'll post my fix:

For year, I used to have Year(Date()) which wouldn't work work because in January, I was looking for stats from December 2008, not December 2009. I put this in the criteria:

IIf(Month(Date())=1,Year(DateAdd("yyyy",-1,Date())),Year(Date()))

I believe it means IF month is January, THEN the year is the past year, ELSE it's the current year.

 

[raskew]

Hi -

In the criteria cell of your date field:

>=DateValue([enter mm/yyyy]) And <DateAdd("m",1,DateValue([enter mm/yyyy]))

This will prompt the user to enter mm/yyyy (e.g. 12/2008) and the result will be limited to the entered month/year.

datevalue("12/2008") equates to 12/1/2008

while

dateadd("m", 1, datevalue("12/2008")) equates to 1/1/2009

HTH - Bob

 

[pbaldy]

The functions from that site should adjust themselves. I just tested this and got December 2008 data:

Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)