# Thread: Calculating Age (Access 2000)

1. ## Calculating Age (Access 2000)

I need an EASY way to calculate age in Access 2000, either in a query (preferred) or on a report (not preferred but necessary). I have a DOB field (date of birth configured as date/time).

2. ## Re: Calculating Age (Access 2000)

If you don't want it exact to the day then your query needs a calculated field ,say, AGENow()-[DOB])/365. Assuming your field is called[DOB] and you want your new field to be called [AGE]. This should give you the age in years to 2 (or more) decimal places (i haven't actually tried it but I'm pretty sure).
Peter Herworth

3. ## Re: Calculating Age (Access 2000)

If you need just the years-old (not months, days, etc.) then try something like:
<font face="Georgia">
SELECT Names.Name, Names.DOB, Year(Date())-Year([DOB])+((Month(Date())<=Month([DOB])) And (Day(Date())<Day([DOB]))) AS Age
FROM [Names];
</font face=georgia>
Easy? You decide.

4. ## Re: Calculating Age (Access 2000)

These work for me in calculating age in years and months. HTH.

Function AgeInYears(Bdate As Long)
Dim age
If IsNull(Bdate) Or Bdate > Now Then
age = Null
Exit Function
End If

AgeInYears = Year(Now) - Year(Bdate) + (DateSerial(Year(Now), _
Month(Bdate), Day(Bdate)) > Now)

End Function

Function AgeInMonths(Bdate As Long)
Dim age
Dim AgeTemp
If IsNull(Bdate) Or Bdate > Now Then
age = Null
Exit Function
End If

AgeTemp = DateDiff("m", Bdate, Now) + (Day(Bdate) > Day(Now))
AgeTemp = AgeTemp / 12 - Int(AgeTemp / 12)
AgeInMonths = Format(AgeTemp * 12, "##")

End Function

Select * from table
ORDER BY Ageinyears([dtmBirthdate]), AgeInMonths([dtmBirthdate]);

5. ## Re: Calculating Age (Access 2000)

you can use this in a query

age: DateDiff("yyyy", [BirthDate], Now()) + Int(Format(Now(), "mmdd") < Format([BirthDate], "mmdd"))
or in a form

textbox = DateDiff("yyyy", [BirthDate], Now()) + Int(Format(Now(), "mmdd") < Format([BirthDate], "mmdd"))
it will give the age in whole years

#### Posting Permissions

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