Age Calculator in Microsoft Access: Query, Form & VBA

An age calculator in Microsoft Access is usually a query expression, a form control, or a small VBA function that returns a person’s completed age in years from their date of birth. Access does not have a dedicated built-in “age” function, so the reliable approach is to use DateDiff("yyyy",...) and then subtract one if the birthday has not arrived yet in the year being checked.

The examples below use a table named People and a date field named DateOfBirth. They use Date() for today’s date, handle missing and future dates deliberately, and show how to calculate age on a fixed reference date.

Quick answer: the Access age formula

In Query Design, add a blank column and enter this expression in the Field row:

AgeYears: DateDiff("yyyy",[DateOfBirth],Date())-
IIf(Date()<DateSerial(Year(Date()),Month([DateOfBirth]),Day([DateOfBirth])),1,0)

This calculates the difference between calendar years, then adjusts the result when this year’s birthday is still in the future. Microsoft documents this same general pattern in its Access Calculate age example. See also Microsoft’s references for DateDiff and DateSerial.

1. Set up the date-of-birth field

In Access, open the table that stores your people or records in Design View. A simple example is:

Use a real Date/Time field for DateOfBirth, not a text field containing strings such as “12/10/2000”. Text dates can be interpreted differently depending on regional settings. If the field is blank, decide whether the result should stay blank or whether the record should be flagged for review.

2. Build an age calculator in Microsoft Access with a query

This is useful when you need age beside every record in a datasheet or report. You do not need to save a changing age value in the table: calculate it when the query runs instead.

1. Choose Create → Query Design and add your people table.
2. Add the fields you want to display, such as PersonID, FullName, and DateOfBirth.
3. In a blank Field cell, paste the AgeYears: expression shown above.
4. To exclude rows with a missing or future date of birth in Query Design, add DateOfBirth twice to the grid. Put Is Not Null in one Criteria cell and <=Date() in the other field’s Criteria cell on the same row so both conditions must be true. Alternatively, use the SQL version below.
5. Run the query and confirm the results against a few known birthdays.

Complete SQL View example

For a table named People with fields PersonID, FullName, and DateOfBirth, switch the query to SQL View and use:

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

The WHERE clause leaves out records with a missing birth date or a future date. If you need to investigate those records instead, create a separate validation query with criteria for Is Null or >Date() rather than silently treating an invalid date as a real age.

3. Calculate age on a specific date

For recruitment, admissions, historical reports, or any rule that uses a defined cutoff date, replace the current date with the reference date. If each row has a Date/Time field called AsOfDate, use this calculated field:

AgeOnDate: DateDiff("yyyy",[DateOfBirth],[AsOfDate])-
IIf([AsOfDate]<DateSerial(Year([AsOfDate]),Month([DateOfBirth]),Day([DateOfBirth])),1,0)

If your query should ask for one cutoff date, replace each occurrence of [AsOfDate] with the same parameter prompt, such as [Enter as-of date:]. Access will request a date when you run the query. Use the exact cutoff date specified by the relevant rules; today’s age is not necessarily the correct value for an eligibility decision.

4. Show the calculated age on a form

To display age while someone views a record, open the form in Design View and add a text box. Set its Control Source to the same expression used in the query, without the AgeYears: alias:

=IIf(IsNull([DateOfBirth]),Null,
DateDiff("yyyy",[DateOfBirth],Date())-
IIf(Date()<DateSerial(Year(Date()),Month([DateOfBirth]),Day([DateOfBirth])),1,0))

Give the text box a clear label such as “Age (years)”. Because the expression uses Date(), the displayed age is recalculated when Access evaluates the form expression; it is not a permanently stored number. Test the form after changing a record’s date of birth and after reopening it on another day.

5. Create a reusable VBA age function

If several queries or forms need the same logic, place it in a standard VBA module so you can call one function instead of maintaining the expression in multiple places.

1. Press Alt+F11 to open the VBA editor.
2. Select Insert → Module.
3. Paste the function below into the standard module and save it, for example as modAge.

Public Function AgeAtDate(ByVal BirthDate As Variant, _
                          Optional ByVal AsOfDate As Variant) As Variant
    Dim dBirth As Date
    Dim dAsOf As Date
    Dim years As Long

    If IsNull(BirthDate) Or Not IsDate(BirthDate) Then
        AgeAtDate = Null
        Exit Function
    End If

    dBirth = CDate(BirthDate)

    If IsMissing(AsOfDate) Then
        dAsOf = Date
    ElseIf IsNull(AsOfDate) Or Not IsDate(AsOfDate) Then
        dAsOf = Date
    Else
        dAsOf = CDate(AsOfDate)
    End If

    If dBirth > dAsOf Then
        AgeAtDate = Null
        Exit Function
    End If

    years = DateDiff("yyyy", dBirth, dAsOf)

    If dAsOf < DateSerial(Year(dAsOf), Month(dBirth), Day(dBirth)) Then
        years = years - 1
    End If

    AgeAtDate = years
End Function

In a query, call AgeAtDate([DateOfBirth]) to calculate the age today, or AgeAtDate([DateOfBirth],[AsOfDate]) for a specified date. The function returns Null for a missing/invalid birth date or a birth date after the reference date. If your database’s security settings block VBA, use the query expression instead or resolve the trusted-database setting according to your organisation’s policy.

6. Test the calculation before using it

Use a fixed reference date to check the logic. For example, when the reference date is 13 October 2026, the completed ages should be:

Test at least one record whose birthday is today, one whose birthday is tomorrow, a leap-day birth, a blank birth date, and a future birth date. The first two cases are particularly important because a formula that merely subtracts the birth year can be one year too high.

7. Common mistakes and leap-day handling

• Subtracting years only: Year(Date())-Year([DateOfBirth]) counts calendar-year boundaries, not completed birthdays. Apply a birthday check.
• Saving age in the table: stored age becomes stale after a birthday. Usually calculate it in a query, form, or report instead.
• Using text for dates: store birth dates as Date/Time values and check regional date entry settings.
• Ignoring missing or future dates: use validation criteria, and do not treat a future DOB as a valid negative age.
• Overlooking February 29: this formula uses DateSerial, which rolls an out-of-range day into the following month; it therefore treats 1 March as the birthday in a non-leap year. If a legal, HR, or eligibility policy specifies a different February 29 convention, implement and test that rule explicitly.

For a general check outside Access, you can compare results with our online age calculator. For other database syntax, see our guide to an age calculator in SQL; SQL dialects differ, so do not copy a SQL Server, MySQL, PostgreSQL, or SQLite expression into Access without checking its syntax.

Frequently asked questions

Does Microsoft Access have a built-in age function?

No dedicated age function is built in. Microsoft’s Access documentation demonstrates calculating age with DateDiff and a check against the birthday in the current year, or by writing a custom VBA function.

Why does DateDiff return an age that is one year too high?

DateDiff("yyyy",...) counts year intervals, so it can return 1 for dates on 31 December and 1 January even though only one day has elapsed. Subtract one when the birthday for the reference year has not yet occurred.

Can I calculate age at a past date?

Yes. Replace Date() with the specific reference-date field or a query parameter, and use that same date in both the DateDiff and birthday-check parts of the expression.

Can this function return years, months, and days?

The examples here return completed years. A years-months-days result needs additional month and day calculations, each with its own borrowing and leap-day rules. Keep that logic separate rather than assuming the year-only result contains the full age breakdown.

Conclusion

An age calculator in Microsoft Access should calculate completed age from the DOB whenever a query, form, or report needs it, rather than store a number that becomes stale. A query expression using DateDiff("yyyy",...) plus a birthday adjustment is usually the simplest approach. Use a form control for a live display, or a reusable VBA function if several objects need the same logic. Validate dates and confirm the intended February 29 convention before using the result for eligibility or other consequential decisions.

Sources checked on 13 October 2026

• Microsoft Learn: Calculate age in Access (last updated 13 September 2021).
• Microsoft Support: DateDiff Function (Access for Microsoft 365, Access 2024, 2021, 2019, and 2016).
• Microsoft Support: DateSerial Function (Access date construction and normalization behavior).