Here is a function to calculate age. The code could be placed in any module.
An example of use for a control's ControlSource property would be: =AgeInYears([DOBField], Date())
An example of use for a calculated field in a query would be: AgeCalcField: AgeInYears([DOBField], Date())
Public Function AgeInYears(ByVal pvarBirthDate As Variant, ByVal pvarCompareDate As Variant) As Long ' Purpose: Calc age in years as of the compare date. ' Accepts: pvarBirthDate date ' pvarCompareDate date ' Returns: number ' Remarks: ' Returns 0 if any needed fields blank. ' ' A simple DateDiff("yyyy",[pvarBirthDate],[pvarCompareDate]) is off ' by one when the month & day of the birthdate is greater than ' the month & day of the compare date. ' ' Access stores the date AND time in each of the date/time fields. ' So even though the fields may be formatted to LOOK just like ' date only, there also is a time stored. ' And the time stored is usually a constant 12:00:00 AM ' (effectively 0) which can be seen if you set the field to be ' long time format. Or even in General Date format any time but ' 12:00:00 AM is shown. It's usually a good idea to store a current date ' using Date() instead of Now() unless one really does want a time ' other than 12:00:00 AM to be stored.
On Error GoTo Err_AgeInYears
' Exit if some fields null. If IsNull(pvarBirthDate) Or IsNull(pvarCompareDate) Then AgeInYears = 0 Exit Function End If
' Calc age in years and subtract 1 if the birth month/day is not on or before the ' compare month/day. ' (I.e. 8/1/1995 birthdate and 9/1/1996 compare date would be age of 1 ' and 9/1/1995 birthdate and 9/1/1996 compare date would be age of 1 ' but 9/2/1995 birthdate and 9/1/1996 compare date would be age of 0.) If (Month(pvarBirthDate) < Month(pvarCompareDate)) Or (Month(pvarBirthDate) = Month(pvarCompareDate) And Day(pvarBirthDate) <= Day(pvarCompareDate)) Then AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) Else AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) - 1 End If
Err_AgeInYears: MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation Exit Function End Function