Crosstab - Null to Zero

[learning_codes]

Hi,

*************** First Question

I'm having touble getting error message.

Val(Nz([FieldName),0))

Is that correct ?

*************** Next question

I'm trying to convert "1" to "X"

I got the count but look "1" for every questions by crosstab. I'm
looking how to change from "1' to "X" but the end of the report I want
it show the total (sum) on the column of "X".

I would appreciate your help if you get back to me asap.

Thanks,

 

[KARL DEWEY]

Use a calculated field ---
Show_X: IIF([YourField] >0, "X", "")
or
IIF([YourField] >0, "X", "") AS Show_X
based on where you install it (Design view grid or SQL statement).

 

[Michel Walsh]

From a crosstab? Edit it in SQL view, and change:


TRANSFORM COUNT(whatever)
SELECT ...


into


TRANSFORM Nz( COUNT(whatever), 0 )
SELECT ...


Sure, in theory, a COUNT (it can be something else, in your case, such as
LAST, FIRST, SUM, MAX, MIN, ... ) can hardly be NULL, but in a 'cell' of the
crosstab, if there is no data in the original table matching the group (row
of the crosstab) and the column of the crosstab, the 'cell' keeps its
initially assigned null value, but Nz( a, b) forces the change of null into
0.



Untested, but you can try:


TRANSFORM SWITCH( 1 = Nz(COUNT(whatever), 0), "x", true, "o" )
SELECT ...


which should change your 1's into x and anything else into o.



Vanderghast, Access MVP