Access does not include a function that will calculate the age of a person or thing based on a given date. This topic contains Visual Basic for Applications (VBA) code for two custom functions, Age and AgeMonths, that will calculate age based on a given date.
The following function calculates age in years from a given date to today's date.
Function Age (varBirthDate As Variant) As Integer Dim varAge As Variant If IsNull(varBirthdate) then Age = 0: Exit Function varAge = DateDiff("yyyy", varBirthDate, Now) If Date < DateSerial(Year(Now), Month(varBirthDate), _ Day(varBirthDate)) Then varAge = varAge - 1 End If Age = CInt(varAge) End Function
The following function calculates the number of months that have transpired since the last month supplied by the given date. If the given date is a birthday, the function returns the number of months since the last birthday.
Function AgeMonths(ByVal StartDate As String) As Integer Dim tAge As Double tAge = (DateDiff("m", StartDate, Now)) If (DatePart("d", StartDate) > DatePart("d", Now)) Then tAge = tAge - 1 End If If tAge < 0 Then tAge = tAge + 1 End If AgeMonths = CInt(tAge Mod 12) End Function
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.