display zeros in crosstab query

[JW Rutgers]

I am trying to create a crosstab query. I have created 53 column headings
with week numbers 1;2;3;4; etc.
I would like the blank values to have 0. I have tried nz but this doesn't
work. The format 0;0;0;0 does work, but not when I import the query results
into Excel where blanks are shown again.

Is there a solution?

 

[Allen Browne]

Switch the crosstab query into SQL view (View menu).

Add Nz() to the field in the TRANSFORM clause (first line).

You will end up with something like this:
TRANSFORM Nz(Sum(tblInvoiceDetail.Quantity),0) AS SumOfQuantity

 

[JW Rutgers]

It works fine, but somhow or other Excel doesn't recognize the NZ function
and I end up with data in Excel which I cannot calculate anymore.

 

[Allen Browne]

Really? I would have expected JET to get that one right, but sometimes it is
*really* dumb about the data types. Try typecasting so it is explicitly a
number:
TRANSFORM CLng(Nz(Sum(tblInvoiceDetail.Quantity),0)) AS SumOfQuantity

Use CCur() if you want currency, or CDbl() for fractional numbers.

More on typecasting in this article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[JW Rutgers]

I don't know why, but after lots of trial and error I discovered that this
expression in the value column of the crosstab query works fine for me:

nz(Count(IIf(Not IsNull([NR]);[NR];0));0)+0

 

[Allen Browne]

Adding the number zero would force JET to treat the result of Nz() as a
number.

You should be able to get the same result by explicitly typecasting with
CLng(), rather than adding the zero.

In any case, you have it working, so that's good.