Knowledge Base

INF: Nz Function Returns Zero-Length String in Query

Article ID: 186310

Article Last Modified on 1/23/2007

Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

This article was previously published under Q186310

Novice: Requires knowledge of the user interface on single-user computers.

SYMPTOMS

In the versions of Microsoft Access listed at the beginning of this article, if you use the Nz() function in a query expression on a Null value in a number field, the Nz() function returns a zero-length string value and not a zero as expected.

CAUSE

Microsoft Access Help for the Nz() function states that the second argument, ValueIfNull, is optional and goes on to say:

   If the value of the variant argument is Null, the Nz function returns
   the number zero or a zero-length string, depending on whether the
   context indicates the value should be a number or a string.

This is incorrect if you use the Nz() function in the manner stated in the "Symptoms" section. A field with a data type of Number will be converted to a string. As a result, a Null will return a zero-length string.

RESOLUTION

To return a string value of zero, use the optional second argument ValueIfNull to specify the use of "0" in place of the Null.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new blank database.
Create a table (Table1) with a numeric field (Field1).
Randomly populate Field 1 with five records containing numbers and five blank (Null) records for a total of ten records.
Create a query (Query1) with the following field expression:
```
      X:NZ([Field1])
						
```
Run the query. You should see ten string data type records, five records that contain left-justified numbers, and five records that are blank.

REFERENCES

For more information about the Nz() function, search for "Nz," and then "Nz Function" using the Microsoft Access Help Index.

Keywords: kbdocerr kbprb KB186310