to search year 2011 for date field

Harrold

Registered User.
Local time
Tomorrow, 06:14
Joined
Mar 17, 2011
Messages
72
Hi

What criteria shall i use to look for transactions for whole year of 2011 in date field when it contains other year also?

Thanks
 
Code:
between #01/01/2011# and #12/31/2011#
 
Can also use a dynamic filter that you do not have to change every year:

Between DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date()), 12, 31)
 
Where Year([your field])=2011

Desipte its simple expression, this is not an efficient solution. The function must be applied to every record in the table to determine the year and this is time consuming in itself. Moreover the query cannot use the index to select the records.

Using a date range as the criteria allows the records to be returned from the index and can be literally hundreds of times faster. It is not really noticeable for a small table becomes increasingly obvious as the number of records grows.
 
I accept the point but can't find where you told the poster to make sure his date field is indexed, not sure that somebody needing to ask the question would have appreciated that.

Brian
 
I accept the point but can't find where you told the poster to make sure his date field is indexed, not sure that somebody needing to ask the question would have appreciated that.

It is implicit in my explanation but I see your point.

Further to that, it is a general principle of database design that any field being searched or joined should be indexed. Date fields are almost always searched in a database so it would be rare to have a date field without an index.

Aside from this, even without an index, using the date range will be considerably faster than applying the function to every record.
 

Users who are viewing this thread

Back
Top Bottom