Microsoft Access, VBA, and VB6 include a wide range of built-in Date functions, including DateDiff, which calculates the difference between two dates. However, there is no built-in way to calculate a person (or an item's) age. DateDiff doesn't work, because it doesn't take into account whether the birthday already arrived in the current year.
The following function accurately calculates Age in Years:
Public Function AgeYears(ByVal datBirthDate As Date) As Integer ' Comments: Returns the age in years ' Params : datBirthDate Date to check ' Returns : Number of years ' Source : Total Visual SourceBook On Error GoTo PROC_ERR Dim intYears As Integer intYears = Year(Now) - Year(datBirthDate) If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then ' Subtract a year if birthday hasn't arrived this year intYears = intYears - 1 End If AgeYears = intYears PROC_EXIT: Exit Function PROC_ERR: MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears" Resume PROC_EXIT End Function
This is an example of calling the function in module code:
' Calculate the current age of a person born on #12/31/1985# Dim CurrentAge As Integer CurrentAge = AgeYears(#12/31/1985#) MsgBox "The current age is: " & vbCrLf & CurrentAge
You can also use the Immediate Window (Ctrl G):
In a query, the function can be used to create a calculated field with a Birthday date field passed as the parameter to display the age of every record in your table.
This code and much more is available in Total Visual SourceBook.
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."
Van T. Dinh, Microsoft MVP