More Nz, IIF errors in report from crosstab query

[bigaustin]

Hi there, found many posts already that seem to address this issue. However, somehow none of them have helped (yet). Any suggestions to the following?

I have a crosstab query, one row heading, one column heading, one value. In my report, I can correctly display all fields if done normally (by drag-n-drop'ing the field into the report). However, I want to use Nz() or IIF() to make the report show "0" instead of nothing.

In total, there are four fields. Two of them display properly, two of them do not. All contain the same type of data: numeric data from a Count. The two that do not display properly just return errors.

I have tried to diagnose this in a number of ways. Let us call two fields [tt]GoodField[/tt] and [tt]BadField[/tt]. As expected, =Nz([GoodField],0) returns [tt]0[/tt] if GoodField would have displayed nothing, and displays the actual value if it exists. =Nz([BadField],0) just returns [tt]#Error[/tt] though. I tried the IsNull() function--again, as expected, =IsNull([GoodField]) returns [tt]-1[/tt] when =[GoodField] would have returned nothing. And again =IsNull([BadField]) returns [tt]#Error[/tt]!

In fact, just =[BadField] returns an error as well! Any suggestions are greatly appreciated.

 

[Steve101]

I can't track to exactly what the problem is that you're explaining above, but hopefully the following will help:

(a) The crosstab query itself will not be able to replace nulls with zero for row/column data which do not appear in the source table data. If you think about it, since no data is represented in the source data set, the crosstab just ends up with a 'hole' in it at the row/column intersection point.

(b) Using nz(YourField,0) in the crosstab query is OK, but all that this will do is ensure that NULL values within the data are included in the Count of the corresponding row/column intersection. It still leaves NULL in those row/column positions where no data is present in the source data set.

(c) One way to address your problem is to use a second query which is based on the first query.

(d) For example, if you have an input table called tblYourTable, with three fields RH, CH and V representing the RowHeading, ColHeading and Value, with the following data:

[tt]
RH CH V
a b 4
a b 2
a g 3
a g 1
b b 6
b g 8
b g
b g 5
c b 9
c b 9
[/tt]

Define a crosstab query (Ive called it qryYourCrosstab), as follows:

TRANSFORM Count(nz([V],0))
SELECT RH
FROM tblYourTable
GROUP BY RH
PIVOT CH;

Then the Second query would look like this:
[ignore]
SELECT RH,
nz(,0) AS B,
nz([g],0) AS G
FROM qryYourCrosstab;
[/ignore]

(e) Applying your second query now, will essentially replace the null B and G values returned by the crosstab with zeros, to achieve the result you want.

I hope that this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[bigaustin]

Thank you! Your post gives me much food for thought, regarding this particular problem and several other issues I've been trying to get my mind wrapped around... =)

-Stephen Fuqua