Compute age from a date

J

Jeff Boyce

Check your favorite search engine and/or mvps.org/access for MS Access and
"calculate age".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

Klatuu

Public Function CalcAge(Bdate As Variant, Optional DateToday As Variant) As
Integer
Dim dtmToDate As Date

' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday
' Returns 0 if Bdate is Null
' Uses current date if DateToday is missing or Null

If IsNull(Bdate) Then
CalcAge = 0
Else
If IsMissing(DateToday) Then
dtmToDate = Date
Else
dtmToDate = Nz(DateToday, Date)
End If
If Month(dtmToDate) < Month(Bdate) Or (Month(dtmToDate) = _
Month(Bdate) And Day(dtmToDate) < Day(Bdate)) Then
CalcAge = Year(dtmToDate) - Year(Bdate) - 1
Else
CalcAge = Year(dtmToDate) - Year(Bdate)
End If
End If
End Function
 
J

John W. Vinson/MVP

crimsonk91 said:
How do I compute the age of someone from their birthdate?
Click to expand...

Copy and paste the function Dave posted - or if you want to avoid code, put
a calculated field in a query:

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Replace Date() by a literal date in # delimiters or by another field or form
reference if you want the age as of some selected date.
 
Top