Sum Rows where there is a Null Value in a Column (1 Viewer)

Gordon

Gordon
Local time
Today, 12:36
Joined
Nov 25, 1999
Messages
34
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

Gordon
Local time
Today, 12:36
Joined
Nov 25, 1999
Messages
34
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

split with a cherry atop.
Local time
Today, 05:36
Joined
Sep 1, 2005
Messages
6,318
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

Gordon
Local time
Today, 12:36
Joined
Nov 25, 1999
Messages
34
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.
 

Users who are viewing this thread

Top Bottom