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.

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

? 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.


Related Information

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"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

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS