How to fill out the empty space with 0 in CrossTab Queries?

[Guest]

When I create a Crosstab query, there are always some blank entries Because
there is actually no data in that combination. Is there anyway to give it a 0
instead of blank?Because when I export the query result into Excel, 0 instead
of blank will same me a lot of trouble revising the formulas to accomodate
those blanks. Thanks a lot!

 

[fredg]

When I create a Crosstab query, there are always some blank entries Because
there is actually no data in that combination. Is there anyway to give it a 0
instead of blank?Because when I export the query result into Excel, 0 instead
of blank will same me a lot of trouble revising the formulas to accomodate
those blanks. Thanks a lot!

Use the Nz() function.

TRANSFORM Nz(Count(YourTable.AField),0) AS CountOfAField
etc....

 

[Duane Hookom]

I will generally wrap the Nz() inside of Val(...Nz().....) just to make sure
the result is treated like a numeric value.