# Thread: Caluate Age In Years And Months (97)

1. ## Caluate Age In Years And Months (97)

I have a query that caluates the age of an individual.
It's: DateDiff("yyyy",[DOB],Date())-IIf(Format
([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
This works, but it only gives me the years. I would like
the results to show years and months (e.g., 40 years, 8
months). Any suggestions?

2. ## Re: Caluate Age In Years And Months (97)

you could try using the mod function & the datediff function together something like this

DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) mod 12

should give you the left over months, your original expression gives you the years

hmmmm was just thinking you might need to play around with the second part of the expression to see if you need to include the - iif part for the month calculations.

good luck

3. ## Re: Caluate Age In Years And Months (97)

Try this expression:

(12+Month(Date())-Month([DOB])+(Day(Date())<Day([DOB]))) Mod 12

Note: the 12+ is there to avoid getting negative results.

4. ## Re: Caluate Age In Years And Months (97)

I only got years again?

5. ## Re: Caluate Age In Years And Months (97)

See the attached Access 97 database (zipped).

It contains a table tblBirthdays and a query qryAge that demonstrates calculating the age in years and months.

This is the complete SQL statement of the query:

SELECT tblBirthdays.FirstName, tblBirthdays.DOB, DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd")) AS AgeYears, (Month(Date())-Month([DOB])+12+(Day(Date())<Day([DOB]))) Mod 12 AS AgeMonths
FROM tblBirthdays;

#### Posting Permissions

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