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

--
newUser51

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.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Ian" <(E-Mail Removed)> wrote in message
news1D0943C-A818-4219-BE80-(E-Mail Removed)...
> 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
>
> --
> newUser51

Many thanks Doug.
Query working a treat !
--
newUser51


"Douglas J. Steele" wrote:

> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "Ian" <(E-Mail Removed)> wrote in message
> news1D0943C-A818-4219-BE80-(E-Mail Removed)...
> > 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
> >
> > --
> > newUser51
>
>
> .
>