Crosstab and nulls

[Guest]

Hi

I have a crosstab which counts dates based on headings ie training types,
however this produces null values obviously where there aer no dates, not a
problem...

just wanted to ask if there was a way of substituing a null for a 0, sure i
can use the nvl function but not sure if this works in a cross-tab.....

any pointers appreciated...

 

[Allen Browne]

Switch the crosstab in SQL View (View menu, from query design view.)

Add the Nz() function to the first line to specify a zero for null.

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

 

[Duane Hookom]

I would add one other function to Allen's solution to correct for Nz()
returning a variant data type rather than a numeric.

TRANSFORM Val(Nz(Sum(Table1.Quantity),0)) AS SumOfQuantity

 

[Allen Browne]

Yes, Duane's suggestion is a good idea.

JET can misunderstand the output from Nz(), so using Val() would force it to
understand the result as a (fractional) number.

If the crosstab should ouput the value as currency, use CCur(), or for whole
number, use CLng().