PRB: Query Results Appear Blank Using the NZ() Function

Article ID: Q153535
Creation Date: 11-JUL-1996
Revision Date: 20-SEP-1996

The information in this article applies to:

Microsoft Access version 7.0

SYMPTOMS

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

If you use the NZ() function in an expression in a query without using the ValueIfNull argument, the results will be empty in the fields that contain Null values.

CAUSE

In Microsoft Access for Windows 95, the NZ() function requires the second argument, ValueIfNull, when used in a query.

RESOLUTION

You can use one of the following methods as a workaround.

Method 1

Use the CINT() function outside of the NZ() function to convert the value to an integer. The following example demonstrates how to use the NZ() function with the CINT() function:

   CINT(NZ([fieldname]))

Method 2

Pass the second argument in the NZ() function. This method returns values of type Variant. This method is more appropriate when using the NZ() function for text fields. The following example demonstrates using the NZ() function for a text field and supplying the second argument:

   NZ([firstname], "Name not Available")

MORE INFORMATION

Microsoft Access for Windows 95 Help states that the second argument, ValueIfNull, is optional; however, using the function in a query without the second argument, does not return (0) zero as expected. It returns a zero-length string of type Variant.

REFERENCES

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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.