ACC2: How to Replace Nulls with Zeros in Crosstab Query Results

Last reviewed: June 20, 1997
Article ID: Q132140
The information in this article applies to:
  • Microsoft Access version 2.0

SUMMARY

This article demonstrates two methods you can use to replace null values with zeros in crosstab query results.

MORE INFORMATION

When you run a crosstab query to summarize data in a row-and-column format, you may have blank (or null values) in some cells. You can replace the nulls with zeros by using the IIF() function or the custom NullToZero()function that is included in the sample database NWIND.MDB within an expression in the Crosstab column of the QBE grid.

NOTE: If you want to use the custom NullToZero() function in your database, you can copy or import it from the Utility Functions module in the sample database NWIND.MDB and paste it into a new or an existing module in your database.

Using the IIF() Function

To replace nulls with zeros using the IIF() function, follow these steps.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

  1. Open the sample database NWIND.MDB.

  2. Create the following new query and add the Orders table and the Order Details Extended query:

          Query: Test1
          -----------------------------------------------------------------
          Type: CrossTab Query
          Join: Orders.[Order ID] <-> [Order Details Extended].[Order ID]
    

          Field: Product Name
    
             Table: Order Details Extended
             Total: Group By
             CrossTab: Row Heading
          Field: Format([Order Date],"yyyy") & " Total"
             Table: <leave blank>
             Total: Group By
             CrossTab: Column Heading
          Field: IIF(Sum([Extended Price]) Is Null,0,Sum([Extended Price]))
             Table: <leave blank>
             Total: Expression
             CrossTab: Value
             Format : Currency
    
    

  3. Run the Test1 crosstab query. In the Product Name column, look for "Chocolate" (without the quotation marks). Note that this record has a zero ($0.00) in the 1991 and 1994 columns.

Using the NullToZero() Function

To replace nulls with zeros using the custom NullToZero() function, follow these steps:

  1. Open the sample database NWIND.MDB.

  2. Create the following new query and add the Orders table and the Order Details Extended query:

          Query: Test2
          ---------------------------------------------------------------
          Type: CrossTab Query
          Join: Orders.[Order ID] <-> [Order Details Extended].[Order ID]
    

          Field: Product Name
    
             Table: Order Details Extended
             Total: Group By
             CrossTab: Row Heading
          Field: Format([Order Date],"yyyy") & " Total"
             Table: <leave blank>
             Total: Group By
             CrossTab: Column Heading
          Field: NullToZero(Sum([Extended Price]))
             Table: <leave blank>
             Total: Expression
             CrossTab: Value
             Format : Currency
    
    

  3. Run the Test2 crosstab query. In the Product Name column, look for "Chocolate" (without the quotation marks). Note that this record has a zero ($0.00) in the 1991 and 1994 columns.

NOTE: In Microsoft Access 7.0 and 97, you can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null.

REFERENCES

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

Microsoft Access "User's Guide," version 2.0, Chapter 12, "Advanced Queries," pages 279-285


Additional query words: fill empty
Keywords : kbusage QryCross
Version : 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


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.

Last reviewed: June 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.