ACC2: How to Replace Nulls with Zeros in Crosstab Query Results
ID: Q132140
|
The information in this article applies to:
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.
- 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: <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
- 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:
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
- 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.
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
Issue type : kbhowto