tel 01732 833085
e david wallis

home > technical tips > access tip

microsoft access 97, 2000, 2002 (XP), 2003
coping with null values in query expression fields

question

We're working on a query in Access 97. 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?

answer

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.

disclaimer

DMW Consultancy Limited does not accept any liability for loss or damage to data to which any of our Technical Tips solutions are applied. Back up your data; test thoroughly before using on live data.