Blank or Null in a Cross Tab Query in Access 2003

  • Thread starter Thread starter PerryK
  • Start date Start date
P

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,

Thanks for the quick response.

That did it!!!
--
Perry K


Allen Browne said:
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.


PerryK said:
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
Click to expand...
Click to expand...
 
Back
Top