How to Fix Access DateDiff Formula Returns an Error
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.

- 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.
Verify the exact names of your form controls in the Property Sheet before modifying the expressions, as Access requires exact matches to calculate correctly.
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.
Right-click your form in the navigation pane and select 'Design View'.
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'.
Select the text box where you want the age to display. In the Property Sheet, go to the 'Control Source' property.
Update the expression to: =DateDiff("yyyy", Forms![YourFormName]![DOB], Forms![YourFormName]![Visit_Date]).
Adjust the Formula for Accurate Age Calculation
Use a more robust formula because a simple DateDiff calculation can be inaccurate until the person's birthday has actually occurred in the current year.
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. Download the Installer: Visit the official WPS Office website and download the free version for your operating system.
- 2. Install WPS Office: Run the setup file and follow the on-screen instructions to complete the installation.
- 3. Enjoy Seamless Compatibility: Open WPS Spreadsheet, Writer, or Presentation to easily manage your daily office files.

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.




