display zeros in crosstab query

  • Thread starter Thread starter JW Rutgers
  • Start date Start date
J

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?
 
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
 
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.
 
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
 
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
 
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.
 
Back
Top