Criteria/iif statement

[tialongz]

I have a table with a lot of blanks or null fields. I build a crosstab from this table. Now, I am trying to replace all the nulls with zeros. So I tried =iif(isnull([2004]),0,[2004]) in the criteria box in the crosstab, but an error message appeared said...you cannot issue a criteria in this field.
error message: You cant specify criteria on the same field for which you entered value in the crosstab row.

Anyhow know how to do this?

Thanks in advance.

[OBP]

tialongz, the easiest way to do this is to replace all the Nulls with zeros in the table.
Create a Select Query using the Field(s) that have zeros, check that that is all it lists in your query.
Change your query type to Update and in the "Update to" field type in 0.
Run the query to replace all of the Nulls with zeros.

One caution, do not change any Null Dates to 0 as this will set the date to something like 01/01/1900.

[XLGibbs]

I have a table with a lot of blanks or null fields. I build a crosstab from this table. Now, I am trying to replace all the nulls with zeros. So I tried =iif(isnull([2004]),0,[2004]) in the criteria box in the crosstab, but an error message appeared said...you cannot issue a criteria in this field.
error message: You cant specify criteria on the same field for which you entered value in the crosstab row.

Anyhow know how to do this?

Thanks in advance.

You would actually do that in the column header as an expression, instead of the criteria expression.

Since the crosstab will be summing and/or other function on the values, I am not sure why replacing them with zeroes is required..

A column with calculated results from another column would look like this (using your column [2004] as a sample) in the column name/header of the design view.

NewColumnName: IIF(isnull([2004]),0,[2004])