MS Access Functions

Calculate Age from a Birth Date in Microsoft Access

Subtracting birth years is not enough to calculate age: the birthday may not have occurred yet in the current year. A correct Access calculation first finds the year difference and then subtracts one when the anniversary date is still ahead.

Last updated: August 28, 2026.

Age calculation in a query

For records with a valid BirthDate, add this calculated field in Query Design:

Age: DateDiff("yyyy", [BirthDate], Date())
     - IIf(
         DateSerial(Year(Date()), Month([BirthDate]), Day([BirthDate])) > Date(),
         1,
         0
       )

In SQL View, the same calculation looks like this:

SELECT PersonID,
       BirthDate,
       DateDiff("yyyy", [BirthDate], Date())
       - IIf(
           DateSerial(Year(Date()), Month([BirthDate]), Day([BirthDate])) > Date(),
           1,
           0
         ) AS Age
FROM People
WHERE BirthDate Is Not Null
  AND BirthDate <= Date();

DateDiff("yyyy", ...) counts year boundaries, so the second part corrects the result before this year’s birthday. The criteria exclude missing and future dates before the expression is calculated.

Calculate age on a particular date

Replace Date() with an as-of date. A parameter makes the query reusable:

PARAMETERS [Age as of:] DateTime;
SELECT PersonID,
       BirthDate,
       DateDiff("yyyy", [BirthDate], [Age as of:])
       - IIf(
           DateSerial(Year([Age as of:]), Month([BirthDate]), Day([BirthDate]))
             > [Age as of:],
           1,
           0
         ) AS Age
FROM People
WHERE BirthDate Is Not Null
  AND BirthDate <= [Age as of:];

Reusable VBA function

A function is preferable when forms, reports, and several queries need the same rule:

Public Function AgeOnDate(ByVal birthDate As Variant, _
                          ByVal asOfDate As Date) As Variant
    If IsNull(birthDate) Then
        AgeOnDate = Null
        Exit Function
    End If

    If CDate(birthDate) > asOfDate Then
        AgeOnDate = Null
        Exit Function
    End If

    AgeOnDate = DateDiff("yyyy", CDate(birthDate), asOfDate)

    If DateSerial(Year(asOfDate), Month(CDate(birthDate)), _
                  Day(CDate(birthDate))) > asOfDate Then
        AgeOnDate = AgeOnDate - 1
    End If
End Function

After saving the function in a standard module, use AgeOnDate([BirthDate], Date()) in a query or form control.

Decide how to handle February 29

In a non-leap year, DateSerial(year, 2, 29) normalizes to March 1. Some organizations instead treat February 28 as the anniversary. That is a business rule, not merely a formatting choice; document it and adjust the function if necessary.

Do not store the current age

Store the birth date and calculate age when needed. A stored age becomes incorrect after the next birthday, while the birth date remains stable.

The important behavior of yearly intervals is documented in Microsoft’s DateDiff reference.

admin

admin

Related Posts