Others

How to Fix Access DateDiff Formula Returns an Error

Elise WilliamsElise Williams Oct 23, 2026 870 views

Question details

The user is encountering an error in a Microsoft Access form when attempting to calculate age or date differences using the DateDiff function.

How to Fix Microsoft Access DateDiff Formula Error
Product
Microsoft Access
Device & OS
not provided
Scenario
Calculating the difference between a Date of Birth (DOB) and a Visit Date within a form control.
Observed behavior
The Access form displays an error instead of the calculated age, likely due to incorrect control references or naming mismatches.
Before you start

Verify the exact names of your form controls in the Property Sheet before modifying the expressions, as Access requires exact matches to calculate correctly.

Solution 1Recommended

Verify Control Names and Use Fully Qualified References

Resolve referencing errors by ensuring the names match the form controls perfectly and using exact path syntax.

Often, the error indicates that a referenced control name does not exist. Using fully qualified references ensures Access knows exactly where to look for the data.

1
Open Design View

Right-click your form in the navigation pane and select 'Design View'.

2
Check Control Names

Click on the controls for Date of Birth and Visit Date. Open the Property Sheet (press F4) and check the 'Name' property under the 'All' tab to ensure they are exactly 'DOB' and 'Visit_Date'.

3
Update the Control Source

Select the text box where you want the age to display. In the Property Sheet, go to the 'Control Source' property.

4
Enter Fully Qualified Formula

Update the expression to: =DateDiff("yyyy", Forms![YourFormName]![DOB], Forms![YourFormName]![Visit_Date]).

Form Name Replacement: Make sure to replace 'YourFormName' with the actual name of the form where the controls are located.
Free Microsoft Office alternative

Boost Your Productivity with WPS Office

While WPS Office does not include a database management tool like Access, it is an excellent, free alternative to Microsoft Office for your word processing, spreadsheet, and presentation needs. Experience a lightweight suite with high compatibility and a familiar interface.

  1. 1. Download the Installer: Visit the official WPS Office website and download the free version for your operating system.
  2. 2. Install WPS Office: Run the setup file and follow the on-screen instructions to complete the installation.
  3. 3. Enjoy Seamless Compatibility: Open WPS Spreadsheet, Writer, or Presentation to easily manage your daily office files.
Highly compatible with Microsoft Excel, Word, and PowerPoint formats.Free, lightweight, and uses minimal system resources.Familiar user interface for a seamless transition from Microsoft Office.Built-in PDF tools to read, edit, and convert documents effortlessly.
microsoft office alternative - wps office

Frequently Asked Questions

Why does DateDiff calculate the wrong age in Access?

DateDiff using just 'yyyy' simply subtracts the year of birth from the current year. It does not consider the month and day, meaning it will show a person as a year older even if their birthday hasn't occurred yet in the current year.

How do I find the correct control name in my Access form?

Open your form in Design View, click on the text box or control, and open the Property Sheet by pressing F4. The 'Name' property under the 'Other' or 'All' tab is the exact control name you must use in your formulas.

What does '#Name?' mean in an Access form field?

The '#Name?' error typically means the form cannot find the field or control referenced in your expression. This usually happens due to a typo in the control name or missing fully qualified references.