# Thread: How do I calculate Age?

1. Registered User
Join Date
Oct 2003
Posts
19

## Unanswered: How do I calculate Age?

I need to calculate the age (Integer) of a person based on their birthday: [Birthday]

I'm currently using:

[Age] = Int(DateDiff("yyyy",[Birthday],Date()))

But it's only caclulating years, and ignoring the date. Example

If the current date is 10/20/03

A [birthday] of 2/4/76 will return an [Age] of 27. But a date of 12/01/76 also returns 27. Only when the birthday passes the new year (01/01/77), will the number will change. Please help!

Thanks!
jneuwirth

2. Registered User
Join Date
Oct 2003
Location
Posts
574
You could check if their birthday has already gone by and add one to the age if it has:

if int(format("mmdd",Date)) > int(format("mmdd",Birthday)) then
age = age + 1
end if

or subtract 1 if need be

3. Registered User
Join Date
Oct 2003
Posts
19
Thanks! jmrSudbury

You know I was thinking the same concept, but I didn't know the exact syntax... I'll try that and let you know

Thanks again.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
You could check if their birthday has already gone by...
the following will do just that

the age goes up on the birthday, and not a day sooner
Code:
```select birthday
, year(date()) - year(birthday)
- iif(month(date()) > month(birthday),0,
iif(month(date()) < month(birthday),1,
iif(day(date()) < day(birthday),1,0)))   as age
from yourtable```

5. Registered User
Join Date
Oct 2003
Posts
19
I appologise for my newness to database design, but I'm not quite sure how to use that code or where to put it. Can that be put into the SQL Code for a query? I'm only familiar with using calculations (=..etc.) and queries in access. I'd love to learn how though!

I actually used jmrSudbury's idea and came up with the following formula entered into a text box on my form and it works! It looks very much like what you suggested:

=IIf(Format(Date(),"mmdd")
>=Format([Birthday],"mmdd"),
Int(DateDiff("yyyy",[Birthday],Date())),
Int(DateDiff("yyyy",[Birthday],Date()))-1)

Thank you for your help, r937.

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
i'm sorry, i don't do access forms

yes, you would paste my query in the SQL window when you create a query, go to Design view, and select View > SQL view

make sure you change the table name to yours

rudy

7. Registered User
Join Date
Oct 2003
Posts
19
Awsome. I think I'm going to start learning to write code so I can really know what I'm doing!

Thanks so much for your help, rudy.

Joey.

#### Posting Permissions

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