Microsoft KB Archive/186310

= INF: Nz Function Returns Zero-Length String in Query =

Article ID: 186310

Article Last Modified on 1/23/2007

-

APPLIES TO


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



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]) </li> Run the query. You should see ten string data type records, five records that contain left-justified numbers, and five records that are blank.</li></ol>

<div class="references_section">