Others

How to Return 0 Instead of #Error in an Access Form Expression

Ayan MasoodAyan Masood Nov 2, 2026 870 views

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.

How to Return 0 Instead of #Error in an Access Form Expression
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Design View

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

2
Locate the problematic control

Select the text box control that is currently displaying the #Error value.

3
Open the Property Sheet

Press F4 or click Property Sheet in the Design ribbon. Navigate to the Data tab.

4
Modify the Control Source

In the Control Source property, wrap your existing reference in the Nz function. Type: =Nz([Credit Taken subform].[Form]![Total], 0)

5
Test the form

Save your changes and switch to Form View to verify that the field now displays 0 instead of #Error.

Use the Nz Function to Handle Blank Values
Troubleshooting Persistent Errors: If it still returns #Error, double-check that the subform control name is correct, verify the form name used by the subform control, and ensure that the referenced control exists and contains a valid numeric expression.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS website to download the free office suite.
  2. 2. Open WPS Spreadsheet: Launch WPS Spreadsheet to start handling your tabular data and calculations.
  3. 3. Import Your Data: Open your existing Excel or CSV data exports to manage your records effortlessly with familiar formulas.
Fully compatible with Microsoft Excel (.xlsx, .xls) files.Free and lightweight, requiring fewer system resources than heavy database applications.Powerful built-in formulas for data validation and error handling (like IFERROR).Familiar user interface for a smooth, learning-curve-free migration.
microsoft office alternative - wps office

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.