Blank or Null in a Cross Tab Query in Access 2003

[PerryK]

I have a basic Cross Tab Query that uses the Count Function to total up
"Events".

The areas that do not have any "Events" show blank cells when I run the query.

The output of this query is used in another query to calculate percentages.

The null or blank cells do not work with the formula for calculating the
percentages.

Is there a way to have the Cross Tab query enter a "0" instead of a "blank"
for the areas with no "Events"?

thanks in advance

 

[Allen Browne]

Switch the query to SQL View (View menu.)
Add Nz() to the TRANSFORM clause.

For example, if it says:
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
change it to:
TRANSFORM CLng(Nz(Sum([Order Details].Quantity),0)) AS SumOfQuantity

Explanation:
http://allenbrowne.com/ser-67.html#DisplayZero

 

[PerryK]

Allen,

Thanks for the quick response.

That did it!!!
--
Perry K

Switch the query to SQL View (View menu.)
Add Nz() to the TRANSFORM clause.

For example, if it says:
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
change it to:
TRANSFORM CLng(Nz(Sum([Order Details].Quantity),0)) AS SumOfQuantity

Explanation:
http://allenbrowne.com/ser-67.html#DisplayZero

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a basic Cross Tab Query that uses the Count Function to total up
"Events".

The areas that do not have any "Events" show blank cells when I run the
query.

The output of this query is used in another query to calculate
percentages.

The null or blank cells do not work with the formula for calculating the
percentages.

Is there a way to have the Cross Tab query enter a "0" instead of a
"blank"
for the areas with no "Events"?

thanks in advance