Others

Calculate Age Between a Start and End Date in Microsoft Access

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user needs to calculate someone's exact age based on a specific end date instead of the current system date.

How to Calculate Age in Microsoft Access Using a Start and End Date

Product: Microsoft Access
Device & OS: not provided
Scenario: Performing a date calculation within a query to determine a precise age between a Date of Birth and an arbitrary target date.
Observed behavior: Standard year subtractions do not account for whether the birthday has already passed in the target year, requiring a more precise formula or function to adjust the age accordingly.

Before you start

Ensure your Access database has a table or query containing both the Date of Birth (DOB) and the target End Date fields formatted properly as Date/Time data types.

Solution 1Recommended

Create a Custom VBA Function to Calculate Accurate Age

Use a custom VBA module function to precisely calculate the age based on a provided end date, automatically checking if the birthday has occurred in the given year.

Using a VBA function is the most reliable method for age calculations in Access because it easily manages leap years and exact month/day comparisons.

Open the VBA Editor

In Microsoft Access, press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

From the top menu, click 'Insert' and select 'Module' to create a blank code module for your database.

Paste the VBA Code

Copy and paste the following code into the module window: Public Function GetAge(varDoB As Variant, Optional varAgeAt As Variant) As Variant If IsMissing(varAgeAt) Then varAgeAt = VBA.Date If Not IsNull(varDoB) Then GetAge = DateDiff("yyyy", varDoB, varAgeAt) - IIf(Format(varAgeAt, "mmdd") < Format(varDoB, "mmdd"), 1, 0) End If End Function

Save the Module

Click the 'Save' icon and name the module something easily recognizable, such as 'modAgeCalculations'.

Call the Function in a Query

Open your query in Design View, select a blank column, and type the following into the Field row: AGE: GetAge([DOB], [End Date]). Run the query to view the correctly calculated ages.

Create a Custom VBA Function to Calculate Accurate Age

Field Name Adjustments: Make sure to replace [DOB] and [End Date] in the query expression with the exact field names used in your specific Access table.

Free Microsoft Office alternative

Need a Lightweight Alternative for Data Processing? Try WPS Office

While Microsoft Access is powerful for relational databases, many age-calculation and data-tracking tasks can be easily managed in spreadsheets. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office, featuring a powerful Spreadsheet tool for all your data management needs.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and CSV data files.Easily calculate ages and date differences using built-in DATEDIF functions without needing to write any VBA code.Lightweight installation package with incredibly fast startup speeds.Familiar user interface makes transitioning from other office suites smooth and effortless.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the DateDiff function directly in a query without VBA?

Yes, you can write an expression directly in the query design grid. For example: DateDiff("yyyy", [DOB], [End Date]) + (DateSerial(Year([End Date]), Month([DOB]), Day([DOB])) > [End Date]). However, utilizing a custom VBA function is often cleaner and easier to reuse across multiple queries.

Why does simply subtracting years sometimes give an incorrect age?

A simple year subtraction (like Year([End Date]) - Year([DOB])) only looks at the year components. It fails to account for the exact month and day, so it cannot determine if the person's birthday has actually occurred yet in the target year.

How do I calculate age based on today's date using this VBA function?

If you want to use the current system date instead of a specific end date, simply omit the second argument when calling the function in your query (e.g., GetAge([DOB])). The function's logic automatically defaults to VBA.Date when the second parameter is missing.

Office Quick Solutions