How to Use Nz Function to Stop Missing-Value Errors in Access Queries

Last updated on 2024-04-25.

The Problem

“We're working on a query in Access. The query has two fields — ‘Number1' and ‘Number2' — that can contain a number or a null. When we create an expression field in the query, Total:[Number1]+[Number2], the total works out fine when both Number1 and Number2 contain numbers.

“The problem comes when one of them is null because the total then becomes null. This is not what we want of course.

“What do we do?”

The Solution

A simple solution to your problem is to use the Nz (null-to-zero) function. Try modifying your expression field to this:

Total:Nz([Number1])+Nz([Number2])

Note that when both Number1 and Number2 have null values, Total returns zero.

Microsoft Access 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013, 2016, 2019 and Microsoft 365 will respond similarly to the Nz function applied in this way.

Hello to the Nz Function

The full syntax of the Nz function is this:

Nz(value_to_assess, value_if_null)

The value_to_assess is the value you want the function to assess.

The value_if_null is what you want the function to return when value_to_assess is itself a null value. This argument is optional. If you omit it from your formula, then Nz assumes it is to assume the value zero

Null stands for no value at all.

You can use the Immediate Window in Access's Visual Basic Editor to test how the Nz function works. Copy and paste any of the examples below into your Immediate Window and then press Enter to see the result.

?Nz(null)

[zero-length string]

?Nz(null,"")

[zero-length string]

?Nz(null,0)

?Nz(null,"Missing value")

The wordsMissing value

?Nz(123,"Missing value")

123

?Nz("XYZ","Missing value")

XYZ

?Nz("XYZ",0)

XYZ

?Nz(null)+Nz(123)

123

?Nz(null)+Nz(null)

?Nz(null,"Missing value")+Nz(null)

Missing value

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

Disclaimer

David Wallis does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.