Sum Rows where there is a Null Value in a Column

[Gordon]

I have a crosstab report with three columns:

Name C W Total
Paul 2 2 4
Jane 5 6 11
John 15

I would like to add a column that sums the values for each name (Total) - as shown above. For instance Paul's 2+2 = 4.

I created a text box in the report with the formula =([c]+[w]). This works, except for the row with John's information, which does not show anything since there is a null value in one of the the columns.

So, two questions.

(1) Is there a way to have the calculated field in the report include Null values?

(2) is there a more elegant way to do this in the crosstab query? I tried adding a column with the following in the query:

Field: Total:Report
Table: Master
Total: Sum
Crosstab: Row Heading

but Access balked and said there is a "Data Mismatch in Criteria Expression."


Thanks for any help.

Gordon

 

[Gordon]

OK, I found a solution to doing this in the report, using the Null Value function:

=Nz([c],0)+Nz([w],0)

However, I suspect that there is a more elegant way to do this in the query? If so, would that also make the report run more quickly?

 

[Banana]

I suppose the alternative is to force all nulls to zero in the crosstab query so the report only has to do sum without nulls. Don't know if it'll make any difference in terms of performance.

 

[Gordon]

I suppose the alternative is to force all nulls to zero in the crosstab query so the report only has to do sum without nulls. Don't know if it'll make any difference in terms of performance.

Sounds like a possible solution. I still figure there must be a way to calculate this in a query, but could not get it to work.