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

## Total Visual SourceBook

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

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