comparing a date with "today" in ACCESS Query

[Ian]

My table has a DOB date. I wish to compare it with today, and count those
rows where DOB is under 16 years, and also count those greater than 16 years.
I thought this would work : datediff("yyyy", CurrentDependents.DOB, Date())
< 17
and datediff("yyyy", CurrentDependents.DOB, Date()) > 16. Alas not.
If I set DOB to 01/01/1994, I get < 17 set to 1, and > 16 set to 0.
If I set DOB to 31/12/1993, I get < 17 set to 0, and > 16 set to 1.
So my syntax is only looking at the year element of the date, and not the
whole date.
Can anyone advise please ?
Many thanks

 

[Douglas J. Steele]

DateDiff works by counting the number of changes that occur between the two
dates. DateDiff("yyyy", #2009-12-31#, #2010-01-01#) will report a 1 year
difference since the year changed between the two dates, even though there's
only a 1 day difference.

Use DateAdd to figure out when they turn 16:

DateAdd("yyyy", 16, DOB)

and compare that to today's date.

 

[Ian]

Many thanks Doug.
Query working a treat !