Calculating Age (Access 2000)

Topic

New Reply

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).

Reply | Quote

Replies

If you don’t want it exact to the day then your query needs a calculated field ,say, AGE:(Now()-[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

Reply | Quote

If you need just the years-old (not months, days, etc.) then try something like:

SELECT Names.Name, Names.DOB, Year(Date())-Year([DOB])+((Month(Date())<=Month([DOB])) And (Day(Date())<Day([DOB]))) AS Age
FROM [Names];

Easy? You decide.

Reply | Quote

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]);

Reply | Quote

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

Reply | Quote