To calculate age accurately, we can still use the
datediff function, but we have to adjust. If you
replace
the straight datediff syntax I used earlier with
the
following, you'll get the result you looking for.
DateDiff("yyyy",[DOB],Now())
+Int(Format(Now(),"mmdd")
< Format([DOB],"mmdd"))
If you're copying and pasting this make sure you
eliminate line wraps.
Let's pull this apart. The first section DateDiff("yyyy",
[DOB],Now()) is the same as before. However,
after this point we get into some tricky bits. Our goal is
avoid counting a year if the person has not yet had a
birthday.
The heart of this calculation takes place with the
following:
+Int(Format(Now(),"mmdd")
Notice that that we have a format function that calls on
a date and formats it as Months and Days: Format
(Now(),"mmdd". This happens twice; once with the
now function and once with our DOB field. It then
compares them with the < symbol.
The second tricky bit is the use of the less than "<"
symbol. We're comparing the two dates and saying
that the current month and day is a less than the
month and day of the birthday. One of two answers is
possible, either today is earlier in the year than the
birthday, or it is not. It returns the answer as a True or
False.
If something is true, Access stores this as a minus
one. If it is false, Access will interpret it as a zero. So if
the month and day is currently less than the month
and day of person's birthday, then we get a negative
1. Otherwise, we get a 0, which we then add back to
the number of years with the "+Int" command. The
Int serves to ensure that a whole number (an
integer for the math fans) is returned.
To be more succinct, the function as a whole takes the
difference in years and, if the person has not had a
birthday yet in the current year, it reduces the
calculation
by one.
Let's see, what happens in the query when these two
different functions are run next to each other.
Unless you're a developer or doing tricky date math,
it's not really critical that you understand each of the
elements of this approach. If you are a casual user,
feel free to just cut and paste the function and use it
whenever you need it. Of course, you'll want to validate
that it works the way you expect it will.