INF: Nz Function Returns Zero-Length String in Query

ID: Q186310

The information in this article applies to:

Microsoft Access versions 7.0, 97

SYMPTOMS

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

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 kbdta QryProb 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 27, 1999