# Thread: Help Needed For Auto Calculation Of Age

1. Registered User
Join Date
Apr 2006
Posts
15

## Unanswered: Help Needed For Auto Calculation Of Age

hi guys.

i got a problem. i want to calculate the excate age of all my members and displayed it on the textbox on the main form.

Below is the codes i keyed in in the property window of the textbox under control source. But it only check for the year. I want to check for the month as well as the day.... how can i go abt doing it?

=DateDiff("yyyy",[Date Of Birth],Date())

Thanks a million in advance

2. Registered User
Join Date
Oct 2003
Location
Ger
Posts
1,969
If you have already stored all the birthdays in your table you can compute the exact age using this SELECT statement

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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
equally you could use the following forumula in a function to calculat ethe age

function CalcAge(dtDoB as date) as string
Dim dtAge As Date
dtAge = Now() - dtDoB 'find the differnce betweent he date of birth and the current date/time
CalcAge = Year(dtAge) - 1900 & " years," & Month(dtAge) - 1 & " months and " & Day(dtAge) & " days"
'can change this for wnythign preferred
'could return "just the age ie xx years and numeric if required
'could return the full date time difference (ie if you knew the precise time born then could also return the hours minutes aswell as the age.
end function

no reason why your couldn't abstract the fucntion with a format type
enum AgeStyle
1= yearsonly
2=YearsandMonths
3 = yearsmonthsanddays
...etc
end enum
eg function CalcAge(dtDoB as date,intStyle as agestyle) as variant

depends on what you want to do....

4. Registered User
Join Date
Apr 2006
Location
Huddersfield, UK
Posts
154
also try, in an unbound text box on either forms or reports

=DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

just copy and paste this code, you may need to change the sections highlighted if your date of birth field is called someting other than DOB (such as birthday)

Dan

5. Registered User
Join Date
Apr 2006
Posts
15
thanks guys for all ur reply...

i tried
=DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

it works well...

#### Posting Permissions

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