Others

How to Calculate Renewal Dates with DateAdd in Microsoft Access

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 869 views

Question details

The user needs to calculate a valid-through date and a renewal date in Microsoft Access by adding a term length in years to an effective date with the DateAdd function.

How to Calculate Renewal Dates with DateAdd in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
The user has an effective date and a term length in years, and wants to compute an expiration date and a renewal date one month before expiration.
Observed behavior
The expected result is a valid expiration date and a renewal date calculated from numeric term years and a valid Date/Time effective date field.
Before you start

Before you start, confirm that the field holding the term length is numeric and the effective date field uses the Date/Time data type in your Access table or query.

Solution 1

Calculate the Valid-Through Date with DateAdd("yyyy", [Term Yrs], [Effective Date])

Use this expression when you need to add the full term years to an effective date to get an expiration or valid-through date.

The interval "yyyy" tells DateAdd to add years. Access returns a Date/Time value, so format the calculated column as a date if it does not display correctly.

1
Open the query in Design View

In Microsoft Access, open the database and open the target query in Design View.

2
Add a calculated field

Click in the Field row of a blank column and enter: ValidThrough: DateAdd("yyyy", [Term Yrs], [Effective Date]).

3
Run the query

On the Design tab, click Run to display the calculated valid-through dates.

Date formatting: If the result appears as a number, set the column Format property to a Date/Time format such as Short Date.
Free Microsoft Office alternative

Use WPS Office as a Free Alternative for Microsoft Office Document Workflows

The DateAdd calculation is a Microsoft Access feature, and WPS Office does not include an Access database engine. For everyday Office work, WPS Office is a free, lightweight suite that opens and saves Microsoft Word, Excel, and PowerPoint formats with a familiar ribbon interface, making migration easy.

  1. 1. Download WPS Office: Download WPS Office from the official WPS website.
  2. 2. Install and launch: Install WPS Office, then open Writer, Spreadsheets, or Presentation depending on the file you need to edit.
  3. 3. Open existing Office files: Open Microsoft Office files and continue editing them in their original formats.
Free access to WPS Writer, Spreadsheets, and Presentation for daily Office workOpens and saves Microsoft Word, Excel, and PowerPoint files with high compatibilityLightweight, familiar ribbon UI helps you migrate from Microsoft Office with a minimal learning curveHandles spreadsheet-based lists, CSV exports, and document tasks often used alongside Access databases
microsoft office alternative - wps office

Frequently Asked Questions

What does DateAdd("yyyy", [Term Yrs], [Effective Date]) do in Access?

It adds the number of years stored in the Term Yrs field to the Effective Date field and returns the resulting expiration or valid-through date.

How do I calculate a renewal date one month before expiration?

Use the expression DateAdd("m", [Term Yrs] * 12 - 1, [Effective Date]). This converts years to months and subtracts one month so the result falls one month before the full expiration date.

Why does my DateAdd expression return #Error?

Check that the term field is numeric, the effective date field is a valid Date/Time field, and the field names in brackets match the query or table exactly.

Can WPS Office run Microsoft Access databases?

No, WPS Office does not include an Access-compatible database engine. WPS Writer, Spreadsheets, and Presentation provide free alternatives for documents, spreadsheets, and presentations.

Do I need quotation marks around the interval in Access DateAdd?

Yes, the interval argument must be a string. Use "yyyy" for years or "m" for months.