Query Criteria Of Like Current Year

[lhooker]

How do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work. Thanks ! ! !

 

[csh2013]

DatePart("yyyy",[FieldName])=2014

 

[lhooker]

csh2013,

Thanks for responding ! ! ! I would like to grab the year from the computer to eliminate the need modify the query every year . . . with a 'LIKE' statement.

 

[Galaxiom]

For superior performance, use a date range in the criteria rather than using Like operator or applying a function to every record in the table.

 WHERE [datefield] BETWEEN DateSerial(Year(Date()),1,1) AND DateSerial(Year(Date()),12,31)

Ensure the datefield is indexed in the table.

 

[csh2013]

I am much less familiar with SQL than Galaxiom who obviously has a good grasp on it. I would have suggested:

DatePart("yyyy",[FieldName])=DatePart("yyyy",Now())

Galaxiom notes that the SQL should be used for best performance. So, while mine will work, you may want to go with the SQL.

 

[lhooker]

Galaxiom,

That worked ! ! ! Thanks for the performance tip also ! ! !

 

[lhooker]

Csh2013,

Thanks for your input ! ! ! I've already applied Galaxiom's solution.

 

[Galaxiom]

DatePart("yyyy",[FieldName])=DatePart("yyyy",Now())

This criterion requires that every record in the table be processed to extract the year from the date before comparing it to the current year.

Using Between allows the engine to select the dates directly from the field's index, return those between the two values calculated in the criteria. The functions are applied only to the criteria, rather than the records.

It is vitally important to realise the difference because Between can be literally hundreds of times faster. The difference only becomes noticable as the number of records increases so what starts out working fine becomes painfully slow over time.

 

[csh2013]

Thanks for the clarification!