Microsoft KB Archive/132140

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

Article ID: 132140

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132140



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

 Open the sample database NWIND.MDB.  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: Total: Group By        CrossTab: Column Heading Field: IIF(Sum([Extended Price]) Is Null,0,Sum([Extended Price])) Table: Total: Expression CrossTab: Value Format : Currency  Run the Test1 crosstab query. In the Product Name column, look for Chocolate. 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:

 Open the sample database NWIND.MDB.  Create the following new query and add the Orders table and the Order Details Extended query:

<pre class="fixed_text">     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: Total: Group By        CrossTab: Column Heading Field: NullToZero(Sum([Extended Price])) Table: Total: Expression CrossTab: Value Format : Currency </li> Run the Test2 crosstab query. In the Product Name column, look for Chocolate. Note that this record has a zero ($0.00) in the 1991 and 1994 columns.</li></ol>

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.

<div class="references_section">