INF: Nz Function Returns Zero-Length String in Query (186310)


The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
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


  1. Create a new blank database.
  2. Create a table (Table1) with a numeric field (Field1).
  3. Randomly populate Field 1 with five records containing numbers and five blank (Null) records for a total of ten records.
  4. Create a query (Query1) with the following field expression: 
          X:NZ([Field1])
  5. 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.
Modification Type:MajorLast Reviewed:10/20/2003
Keywords:kbdocerr kbprb KB186310
©2003 Microsoft Corporation. All rights reserved.