Queryfor this years date (1 Viewer)

[Anthony George]

Hi Everyone

I wonder if you could help me

I have designed a Memo type program to remind me of all of the things in my life I need to keep track of for example:- Names, Addresses, Birthdays, Serial numbers, Expiry dates e.t.c.

It only has 2 fields and a typical record might look like :- 1 Expiry Date: 03/11/15 House Insurance

I need to write a query to examine each record looking for instr Dates in the format shown above and only returning the ones for the current year.

Kindest Regards

Tony

 

[Galaxiom]

If the date is stored as a date datatype rather then a string then:
WHERE Year([datefield]) = Year(Date())

This will also work for a string date because it will implicitly convert the string to a date according to the Windows Regional date format.

It would be more efficient for dates stored as strings to use:
WHERE Right([strDate],2) = Year(Date())

BTW There are implicit datatype conversions in that expression too.

 

[Galaxiom]

While the above is the easy answer note that it requires the query to process every date in the table.

This is a very good reason to store dates as dates because the following much more efficient clause can be applied.

WHERE [datefield] BETWEEN DateSerial(Year(Date()),1,1) AND DateSerial(Year(Date)+1,1,0)

This query can use the index on the date field to directly select the records without further processing.

 

[Anthony George]

Thanks for a very quick reply.
I will give it a try and get back to you.
Regards
Tony

 

[Anthony George]

Hi Galaxiom
Thank's for your help with this one. By extractiing the information you gave me i was able to get my query working.
Thank's Again
Kindest Regards
Tony