How to Fix an Access Report Showing #Error After a Parameter Entry
Question details
Users need to resolve an issue where a Microsoft Access report displays #Error instead of the expected data or zero after entering a parameter.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Generating a report that uses a calculated text box containing a parameter or aggregate expression, particularly when the underlying query returns no records.
- Observed behavior
- The calculated text box on the report shows #Error instead of properly returning the calculated value or defaulting to zero.
Before attempting to modify your report expressions, ensure you have a backup of your Access database and verify that the exact parameter names used in your queries match your intended inputs.
Reference a Control on an Open Form
This is the most reliable workaround. By linking the report to a form instead of relying directly on a parameter prompt, you can prevent evaluation errors when no records are returned.
When an Access report opens without underlying data, calculated text boxes that rely on parameter prompts can fail to evaluate, resulting in an #Error message. Referencing an active form bypasses this limitation.
Create a new blank form in Access and add an unbound text box for the user to enter the parameter. Name the form (e.g., 'frmParameters') and the text box (e.g., 'txtStartDate').
Add a button to this form that opens your report. Ensure the form remains open in the background (you can make it hidden if preferred) while the report is generated.
Open your report in Design View. Select the calculated text box that is displaying the error, go to the Property Sheet, and change its Control Source to reference the form using the syntax: =Forms![frmParameters]![txtStartDate]

Use the Nz() Function to Handle Empty Results
Wrap your expressions in the Nz() function to force Access to return a zero or alternative text when a null value or empty dataset is encountered.
Check for Circular References or Naming Conflicts
Ensure that the name of the calculated text box is completely distinct from the fields bound to the report's underlying query.
Looking for a Lightweight Alternative to Microsoft Office?
While Microsoft Access handles complex relational databases, a significant portion of daily data analysis, reporting, and parameter-based querying can be performed effortlessly using WPS Spreadsheet. WPS Office offers a free, lightweight suite that is fully compatible with major document formats.
- 1. Download the installer: Visit the official WPS Office website and download the free installer package for your operating system.
- 2. Install the suite: Run the setup file and follow the on-screen instructions to complete the fast installation process.
- 3. Open WPS Spreadsheet: Launch WPS Office and open WPS Spreadsheet to easily manage your data, utilize advanced formulas, and create dynamic pivot table reports.

Frequently Asked Questions
Why does my Access report show #Name? instead of #Error?
The #Name? error typically occurs when the Control Source contains a typo, references a field that does not exist in the report's underlying query, or lacks the '=' sign at the beginning of an expression.
Can I prevent a report from opening entirely if there are no records?
Yes. You can use the 'On No Data' event of the report. In the event procedure, type 'Cancel = True' and optionally add a MsgBox to inform the user that no records were found matching their parameters.
Does the Nz() function work in report queries or just on the report design?
The Nz() function works perfectly in both. It is often considered best practice to handle null values at the query level using Nz() so the report receives clean data before rendering the text boxes.




