Crosstab Query show null values

[kon]

I have a Crosstab Query where except the values, shows null values. Is it
possible to show the null values as 0 zero in the query or in the form and
in the report bases on the query ?

Thanks In advance

 

[Allen Browne]

Switch the query to SQL View (View menu, in query design.)
Add Nz() to the TRANFORM line.

As an example, change:
TRANSFORM Sum(Orders.QtySold) AS SumOfQtySold
to:
TRANSFORM Nz(Sum(Orders.QtySold),0) AS SumOfQtySold

 

[Duane Hookom]

I usually wrap the Nz() in Val() to make sure the expression returns a
numeric value.
TRANSFORM Val(Nz(Sum(Orders.QtySold),0)) AS SumOfQtySold

 

[Allen Browne]

That's a good thought, Duane.

Especially in JET 4 (Access 2000 and later), Access is not good at
determining the data type of calculated fields, and Nz() is a major factor
in that problem. Val() forces the result to be interpreted as a number
(probably a Double). If quantities are whole numbers, I would probably
prefer CLng() to force the result to a long.