Null to Zero in a crosstab

[Bill]

Hi all,

I have null values appearing a crosstab which are null values. I want them
to display as zero but NZ and messing with iif does not work which I assume
is because there is nothing at all at the intersection - not even a null!!

Anyone any idea how I display a zero in such circumstances please.

Ta.
Bill.

 

[John Spencer]

It always helps if you post your SQL (View: SQL)

You probably need to change the first line to read something like the
following.

TRANSFORM CDbl(NZ(Sum(YourField),0))
SELECT ....

Sum or Count or Average the value, then wrap that in NZ to return zero,
then wrap that in a number conversion function to force the value back
to a number. Access often (always?) returns a string when you use NZ
function in this type of query.

If that fails, then post the SQL of your query and someone should be
able to modify it to work the way you want.