DMW logo
tel 01732 833085
tonbridge · kent · UK

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

How to use the null-to-zero (NZ) function to prevent expressions in Access query fields causing errors due to null or missing data.

Last updated on 2020-05-26 by David Wallis.


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?”


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.


More to the NZ Function

The full syntax of the NZ function is this:

Nz(value_to_assess,value_if_null)

The value_to_assess can be a number or text string. The value_if_null is what you want the NZ function to return when value_to_assess is null.

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 or 0]

?Nz(null,0)

0

?Nz(null,"Missing value")

Missing value

?Nz(null,"")

[zero-length string]

?Nz(123,"Missing value")

123

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

XYZ

?Nz("ZYZ",0)

XYZ

?Nz(null)+Nz(123)

123

?Nz(null)+Nz(null)

0

?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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification


Disclaimer

DMW Consultancy Ltd 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.

“I hate Nulls … I wish I had known about NZ before.”

Nancy (ca 2004)