Fix Microsoft Access Calculation Fails When Subtotal Fields Are Null
Question details
Adding several Access form fields fails because one or more subtotal controls contain Null values.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Calculating a grand total from multiple subtotal fields on a Microsoft Access form.
- Observed behavior
- The calculation fails or returns an error when any of the referenced subtotal fields are empty, as the system cannot add Null values directly.
Open your Microsoft Access database and note the exact names of the subtotal controls used on your form so you can reference them accurately in your expression.
Use the Nz Function to Convert Null Values to Zero
Wrap each control reference in the Nz() function to ensure empty fields are treated as zero during calculation.
The Nz function in Microsoft Access evaluates a variant and returns a zero, a zero-length string, or another specified value if the variant is Null. By applying it to your subtotal fields, you prevent the calculation from failing.
Right-click your form in the Navigation Pane and select Design View.
Click on the text box where you want the final calculated total to be displayed.
Press F4 to open the Property Sheet. In the Data tab, locate the Control Source property.
Type the expression using the Nz function for each subtotal control. For example: =Nz([SubTotal],0)+Nz([SubTotal2],0)+Nz([SubTotal3],0). Replace 'SubTotal' with your actual control names.

Looking for a Lightweight Office Suite? Try WPS Office
While Microsoft Access handles complex databases, for your everyday document, spreadsheet, and presentation needs, WPS Office offers a powerful, free alternative. Enjoy a familiar interface and seamless migration without the heavy subscription fees.
- 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 downloaded file and follow the simple on-screen instructions to install the suite.
- 3. Open and Edit Your Documents: Launch WPS Office and instantly open your existing Microsoft Office files without losing any formatting.

Frequently Asked Questions
What exactly does the Nz function do in Microsoft Access?
The Nz function evaluates a field's value and returns zero, a zero-length string, or another specified value when the field is empty (Null). This allows mathematical calculations to proceed normally instead of failing due to missing data.
Why do I get an invalid operator error when writing my expression?
This error typically occurs if there is a missing parenthesis, a misplaced comma, or a typo in the control name. Verify that every opening parenthesis has a closing one and that control names exactly match those on your form.
Can I prevent Null values by setting a default value instead?
Yes, you can set the Default Value property of your subtotal text boxes to 0. However, this only applies to new records created after the change. For existing records that already contain Null values, you will still need to use the Nz function.




