Setting a date field in the criteria of a query

[Kiers]

Hi,

I have a report that is based on a crosstab query that inturn is based
on a simple query. I want the simple query to limit the output based
on date - all records generated since the start of the current year.
Is it possible to set the criteria so that it will be correct in
subsequent years? I am looking to produce a similar report for current
month only, so knowing how i can calculate the dates will be very
useful.

TIA

Kiers

 

[Lord Kelvan]

Between "01/01/" & Year(Now()) And Now()

that will select the values between the 01/01/currentyear and
todaysdate

hope that helps

Regards
Kelvan

 

[Lord Kelvan]

Between "01/" & Month(Now()) & "/" & Year(Now()) And now()

should select from the start of the surrent month and todays date
remember you can also do day(datevalue) as well as month and year

 

[Kiers]

Between "01/" & Month(Now()) & "/" & Year(Now()) And now()

should select from the start of the surrent month and todays date
remember you can also do day(datevalue) as well as month and year

Hi Kelvan,

thanks for the solution - both work perfectly

Rgds

Kiers

 

[Marshall Barton]

I have a report that is based on a crosstab query that inturn is based
on a simple query. I want the simple query to limit the output based
on date - all records generated since the start of the current year.
Is it possible to set the criteria so that it will be correct in
subsequent years? I am looking to produce a similar report for current
month only, so knowing how i can calculate the dates will be very
useful.

Why not just check the year?

Year(datefield) = Year(Date())

 

[John Spencer]

Or if you want to use any index available on the field

Where SomeDateField >= DateSerial(Year(Date()),1,1)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================