How to Return 0 Instead of #Error in an Access Form Expression
Question details
The user wants an Access expression that references a subform total to display 0 instead of an #Error value when the source field is blank.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Referencing a subform total in a main Access form where the underlying data might be empty.
- Observed behavior
- The expression returns #Error instead of a blank or zero value.
Ensure you have the exact names for your subform control and the specific field you are referencing, as misspelled names or deleted controls can also trigger expression errors.
Use the Nz Function to Handle Blank Values
Wrap your form expression in the Nz function to replace null or blank values with a predefined number like zero.
The #Error occurs because Microsoft Access cannot evaluate a mathematical expression when the referenced subform field is entirely blank or null. The Nz (Null-to-Zero) function intercepts this null state and replaces it with a specified value, allowing the form to display properly without errors.
Right-click your main Access form in the navigation pane and select Design View.
Select the text box control that is currently displaying the #Error value.
Press F4 or click Property Sheet in the Design ribbon. Navigate to the Data tab.
In the Control Source property, wrap your existing reference in the Nz function. Type: =Nz([Credit Taken subform].[Form]![Total], 0)
Save your changes and switch to Form View to verify that the field now displays 0 instead of #Error.

Manage Your Data Efficiently with WPS Spreadsheet
While Microsoft Access is used for complex relational databases, many data tracking, calculation, and form-like tasks can be managed easily with WPS Spreadsheet. WPS Office is a free, lightweight alternative that is fully compatible with Microsoft Office formats, offering an intuitive interface for seamless transitions and powerful formula tools like IFERROR to handle calculation issues.
- 1. Download WPS Office: Visit the official WPS website to download the free office suite.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet to start handling your tabular data and calculations.
- 3. Import Your Data: Open your existing Excel or CSV data exports to manage your records effortlessly with familiar formulas.

Frequently Asked Questions
What does #Error mean in a Microsoft Access form?
The #Error value typically indicates that Access cannot evaluate the expression in the control's Control Source. This happens when referencing a blank or null field, a misspelled control name, a deleted field, or when dividing by zero.
Why doesn't the IIf(IsNull(...)) function work instead of Nz?
While IIf(IsNull(...)) can check for nulls, it still evaluates both the true and false parts of the expression in Access. If the false part contains an invalid reference to a blank subform, it may still throw an error. The Nz function is specifically designed to safely replace a null with a zero or another string.
How do I find the exact name of my subform control?
Open the main form in Design View, click once on the outer edge of the subform to select the subform container control (not the inner form itself), and check the 'Name' property in the Property Sheet under the 'Other' or 'All' tab.




