Remember that there aren't exactly 365.25 days in a year. However, the
inaccuracy that approximation will introduce probably won't cause many
problems.
The formula Rick B provided is considered far superior, though. It
calculates the number of years between the Birthdate and the date in
question. Because the DateDiff function is a lilttle too literal, and treats
the difference between 31 Dec, 2004 and 1 Jan, 2005 as a year, you need to
adjust the value it returns if the birthday hasn't yet occurred in the test
year (2005, in this case). That's what the
(Format([Birthdate],"mmdd")>Format(Date(),"mmdd")) part is for. That formats
the month and day of birth and compares it to the current month and day. If
the formatted birthdate is greater than the formatted date (i.e.: the
birthday hasn't yet occurred), the expression returns True, or -1, so that
the DateDiff calculation is reduced by one.
BTW, the only reason #30/09/2005# works in your example is because there is
no 30th month. If you were to use #1/10/2005#, Access will treat it as 10
Jan, 2005, regardless of what your regional settings are.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Jesse said:
Hi,
This is what I ended up using...
=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)
...it works perfectly!
As I understand it, the function calculates the difference in days between
[Date of Birth] and Sept 30, 2005 [30/09/2005] and divides this number by
365.25 (to accomodate leap year). This yields decimal values, so the
integer
INT() function is used to display the person's age.
For example, someone born on November 1, 2005 would be 21.91 years old as
of
Sept 30, 2005 - but their age is shown as 21.
-Jesse
:
To calculate the age as of Sept 30, 2005 I would think you could take the
traditional age calculation and simply replace the current date with Sep
30,
2005.
Calculate age...
DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))
Calculate age as of 9/30/2005...
DateDiff("yyyy",[Birthdate],#09/30/2005#)+(Format([Birthdate],"mmdd")>Format
(#09/30/2005#,"mmdd"))
Hope that helps,
MERRY CHRISTMAS!!!
Hi -
I'm using a Form with the field "Birthdate" (Medium date).
I also have a field "Age" that I want to be calculated after I enter in
the
Birthdate.
But I want the "Age" at Sept 30 2005.
So if the Month is Sept or earlier, the expression should be
"2005-YEAR".
But if the Month is Oct-Nov-Dec, the experssion should be
"2005-YEAR-1".
How do I do this?
Any suggestions?
Thanks!
Jesse