Others

How to Display Future Expiration Dates in an Access Report

Emma BrownEmma Brown Nov 2, 2026 869 views

Question details

The user needs to calculate and display a future expiration or recertification date on an Access report without adding separate columns for each course in the underlying table.

How to Display Future Expiration Dates in an Access Report
Product
Microsoft Access
Device & OS
not provided
Scenario
Designing or modifying an Access report to automatically calculate training validities using an expression.
Observed behavior
Requires an expression for the Expression Builder to dynamically calculate future dates based on a completion date.
Before you start

Ensure you have your Access report open in Design View and verify the exact spelling of the date field (e.g., [Date Taken]) from your report's record source.

Solution 1Recommended

Calculate a Fixed Future Expiration Date

Use this method if the expiration or recertification period is a standard, fixed duration across all records, such as exactly 24 months after the training date.

In Microsoft Access reports, you do not use spreadsheet cells. Instead, you utilize unbound text boxes to run calculations directly on the report layout using the Expression Builder.

1
Open Report Design View

Right-click your report in the Access Navigation Pane and select Design View to reveal the report grid and controls.

2
Add an Unbound Text Box

Navigate to the Report Design tab on the ribbon, click the Text Box icon, and click anywhere in the Detail section to insert it. Delete the accompanying label if it is not needed.

3
Enter the DateAdd Expression

Select the new unbound text box and press F4 to open the Property Sheet. In the Data tab, locate the Control Source property and type the expression: =DateAdd("m", 24, [Date Taken]).

Calculate a Fixed Future Expiration Date
Format the Date Display: To ensure the calculated result looks like a proper date instead of a serial number, change the Format property of the text box (under the Format tab) to Short Date.
Free Microsoft Office alternative

Looking for a Lightweight Office Suite? Try WPS Office

While WPS Office does not include a direct database management equivalent to Microsoft Access, it is an exceptionally lightweight, free, and highly compatible alternative for Word, Excel, and PowerPoint. If you frequently export Access reports to spreadsheets or text documents for editing, sharing, or presenting, WPS Office handles them flawlessly.

Fully compatible with Microsoft Excel (.xlsx) formats for opening exported database reports.Lightweight installation and significantly lower system resource consumption.Familiar, tabbed user interface allowing you to handle documents, spreadsheets, and PDFs in one window.Free built-in PDF tools for easily sharing your converted reports securely.
QA img-9

Frequently Asked Questions

What is the syntax for the DateAdd function in Microsoft Access?

The syntax is DateAdd(interval, number, date). The interval defines the time unit (e.g., "m" for months, "yyyy" for years, "d" for days). The number is how many units to add, and the date is the starting date field.

Can I add full years instead of months to calculate an expiration date?

Yes. To add years instead of months, use "yyyy" as the interval string. For example, to add 2 years, your expression would be =DateAdd("yyyy", 2, [Date Taken]).

Why does my text box display #Name? or #Error instead of the future date?

This error typically occurs when the field name specified in your expression does not perfectly match the field name in the report's record source. Ensure the spelling is exact and wrapped in brackets, such as [Date Taken].