How to Display Future Expiration Dates in an Access Report
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.

- 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.
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.
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.
Right-click your report in the Access Navigation Pane and select Design View to reveal the report grid and controls.
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.
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 Variable Expiration Date
Apply this approach if different courses or certifications have varying validity periods (e.g., some expire in 12 months, others in 36 months).
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.

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].




