Age Calculation Function

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

Exit Function

Err_AgeInYears:
MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation
Exit Function
End Function

J. Paul Schmidt, Freelance Access and ASP Developer
http://www.Bullschmidt.com/Login.asp - Database on the Web Demo
http://www.Bullschmidt.com/Access - Sample Access Invoices Database