1. I need to calculate a persons age on a certain year in access

2. Can you clarify what you want.
Do you just want the age in years? or years and months? or years, months and days?

Presumably you have the DOB, and you want to know the age on a specific date? The age within any year will vary depending on the date.

If you only want a rough calculation you can just subtract the Year([DOB]) from the Year you are interested in.

To get a more accurate figure, you need to work out whether they have had a birthday yet, on the day in question.

3. [quote name='buckshot' post='798362' date='16-Oct-2009 12:58']I need to calculate a persons age on a certain year in access[/quote]

To obtain the persons approximate age not taking into account that they may have had a birthday this year use this in a query:

Age: Year(Now( ))-Year([DoB])

To obtain a more accurate age at this current point in time use this in a query:

Age: DateDiff("yyyy",[DoB],Date())+(Format([DoB],"mmdd")>Format(Date(),"mmdd"))

This would be pretty well accurate but if you happen to calculate a date of birth that falls on 29th Feb (Obviously a leap year) and you doing the calculation on 28th Feb on a non-leap year use:

Age: DateDiff("yyyy",[DoB],Date())+((Format([DoB]+(Format([DoB],"mmdd")="0229" And Format(Date(),"mmdd")="0228" And Format(Date()+1,"mmdd")<>"0229"),"mmdd"))>Format(D ate(),"mmdd"))

Enjoy

4. The formula I use is:
Age: iif(DateSerial(Year(Date()),Month([Dob]),Day([DOB]))>Date(), Year(Date())-Year([DOB])-1,Year(Date())-Year([DOB])))

The first bit tests if they have had their birthday this year..then it is either the difference between the years, or one less than 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
•