Microsoft Access, Office/VBA, VB6 Modules royalty-free source code library

Microsoft Access Modules royalty-free source code library

Microsoft Access VBA and VB6 Module Library

Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

Age (Birthday) Calculation in Microsoft Access, VBA, and VB6

Provided by: Luke Chung, FMS President

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.

Age Function in Years

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

Usage Examples

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):

? AgeYears(#12/31/1985#)

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.