1. Registered User
Join Date
Feb 2004
Location
Alpine Califormia
Posts
1,796

earlier post I was inquiring about how to Setup Age as a calculated column. I would love to do that, just not sure how. Can you help with that please. This what I have so far, its in the properties of the Age field so when the users enter the DOB the Age automatically pops up

Code:
`=DateDiff("yyyy",[StudentDateofBirth],Date())+(Date()<DateSerial(Year(Date()),Month([StudentDateofBirth]),Day([StudentDateofBirth])))`

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
calculated column... I'm hoping thats a terminolgy issue, and that you are not storing the age

its a derived value
when ever you need the age of a person then use datediff
however I think you are complicating the second value
just subtract the date of birth from the current date (use date() for preference over now())

if you have, say infants, you may want to do a datediff to find the number of months, then divide by \ 12 to find the number of years for adults. the \ is not a misprint, it forces VBA to use integer maths

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Calculating age is more complicated than that Mark.
This function is too verbose but talks through the options:
Access/VBA Tutorials - Calculate a persons age given the DOB

4. Registered User
Join Date
Jul 2006
Posts
30
This works well. It can be off by one day on the DOB depending on leap year.

AGE: Int((Date()-[DOB])/365.25) (not sure why but starting with Access 2010 this may display as "######" in some queries but will display correctly if sorted.

If you need age at date of death(DOD), use the following

AgeDeceased: IIf(Left([Deceased],1)="Y",IIf([DOD],"Dec" & Int(([DOD]-[DOB])/365.25),"Deceased"),Int((Date()-[DOB])/365.25))

If deceased and DOD is known it will calculate the age on DOD prepended by "Dec". If DOD is not known, it will just insert "Deceased". If not deaceased it will just calculate age as above.

5. Registered User
Join Date
Feb 2004
Location
Alpine Califormia
Posts
1,796
Originally Posted by healdem
calculated column... I'm hoping thats a terminolgy issue, and that you are not storing the age

its a derived value
when ever you need the age of a person then use datediff
however I think you are complicating the second value
just subtract the date of birth from the current date (use date() for preference over now())

if you have, say infants, you may want to do a datediff to find the number of months, then divide by \ 12 to find the number of years for adults. the \ is not a misprint, it forces VBA to use integer maths
sorry calcluated column is not correct please ignore that

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•