Calculate Age from a Birth Date in Microsoft Access

Last updated: August 29, 2026.

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.

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.

Continue with date range queries and DateSerial reference.

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