Crosstab query -- replaced NULL values with 0 (zeros) but replacement values are stored as text

[skydivetom]

Hi:

Please see attached sample DB (as well screenshot).

The default crosstab includes NULL values for n records. I changed the [COUNT] crosstab's SUM value to an expression as follows:

CountStatus: Format(Nz(Sum(Val([Count])),0))

As illustrated, while I now display "zero" (0) values, they are stored as TEXT (vs. NUMBER) even though the expression includes the "Val" reference.

How should the expression be changed so that I still get zeros for missing values but they are stored as a NUMBER (vs. TEXT)?



Thank you,
EEH

[madpiet]

Hi:

Please see attached sample DB (as well screenshot).

The default crosstab includes NULL values for n records. I changed the [COUNT] crosstab's SUM value to an expression as follows:

CountStatus: Format(Nz(Sum(Val([Count])),0))

As illustrated, while I now display "zero" (0) values, they are stored as TEXT (vs. NUMBER) even though the expression includes the "Val" reference.

How should the expression be changed so that I still get zeros for missing values but they are stored as a NUMBER (vs. TEXT)?

Thank you,
EEH

Remove the Format() part. Specify the field types in the query. Format returns a string if I remember correctly.

[CJ_London]

The format function changes values to text. Just remove it and leave the nz function

[skydivetom]

I changed the expression to:

CountStatus: Nz(Sum(Val([Count])),0)

Unfortunately, they values still come out as text. See attached screenshot.

Any other ideas??

[skydivetom]

I swapped the "Val" before the NZ... that did the trick!

CountStatus: Val(Nz(Sum([Count]),0))

[skydivetom]

Thank you both for the recommendation to remove the Format()... I appreciate it.

[CJ_London]

why do you need the Val function? - count returns a number, not text