DMW logo
tel 01732 833085
tonbridge · kent · UK

Access Null Values in Query Expression Fields and the NZ Function

How to cope with null values that in query expressions can cause erroneous results, and, in forms and reports, cause errors in calculations.

Last updated on 2018-08-14 by David Wallis.


Question

“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.


Applies To

This topic is relevant to Microsoft Access 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013 and 2016.


Your Support for dmw TIPS

If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.

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

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

To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —

Invoice

Thanks, in anticipation.


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)