PDB's solution, with its excellent explanation, is the standard way of determining a person's actual
age at any giving point in time.. In general, there's two things you have to keep in mind when using the
DateDiff() function:
- DateDiff() always returns the difference of the interval you specified
- How accurate a date difference do you need?
As you found out, if you specify "
yyyy" as the
Interval argument, Access will return the difference in the
year portion of the two dates. If you use
year as your
Interval, then
12/31/2008 and
1/1/2009 will return a
difference in
years of
1, even though, in actuality, it's only a
difference of
1 in
days!. In other words, Access gives you what asked for!
As I said, you also have to think about the
accuracy you need. Using the dates
11/1/2008 and
12/31/2008
and calculating the
difference in
months
will yield
1, the difference, in
months, between
11/2008 and
12/2008.
Using the
same dates but calculating the
difference in
days
will yield
60, which most people would consider to be
2 months, not
1 month!
To work around this, you need to use
DateDiff() with the
lowest common denominator, if you will, that will give you the accuracy you need, then divide it by
your definition of the
unit of
date or
time. So if you define a month as 30 days, to determine the difference, in
months, between a StartDate of
11/1/2008 and an EndDate of
12/31/2008 you would do this:
Months = DateDiff("d", StartDate, EndDate)/30
which will yield
2 months.
.
Linq
;0)> 
147
