"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
Age 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 their
birthday has arrived yet 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
AgeYears = intYears
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
Below is an example of calling the function:
' 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
This code and much more is available in
Overview of Total Visual SourceBook
The source code in Total Visual
SourceBook includes modules and classes for Microsoft Access, Visual
Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily
add this professionally written, tested, and documented royalty-free code
into your applications to simplify your application development efforts.