including null values in a sum query (non-SQL programmer)

[Guitarzan]

Greetings all,
Here is my issue.
I have 3 fields containing several rows of data retrieved and calculated
from its Table. The query responds with several empty cells obviously no data
to capture.
When trying to sum up the 3 fields however if one of the three cells in that
record is missing data (null) then the ending sum is blank actually bypassing
the other two cells where data does reside.

I tried using the "nz" method. And it did return zeros as it should.
However, my sum column instead of adding the records concatenated the data.

Question: How do I add the 3 fields that contain random null cells so that
my sum column reports the correct sum for each record?

Thanks so much for your expertise!

Rick

 

[KARL DEWEY]

However, my sum column instead of adding the records concatenated the data.
You fields are more than likely are text fields.
Try this --
MySum: IIF([Field1] Is Null, 0, Val([Field1])) + IIF([Field2] Is Null,
0, Val([Field2])) + IIF([Field3] Is Null, 0, Val([Field3]))

 

[Guitarzan]

That worked Thanks much, Karl! Like I said, I am not a SQL programmer. I
appreciate your chowing me how to set it up in the Query window.

Thanks again!

You fields are more than likely are text fields.
Try this --
MySum: IIF([Field1] Is Null, 0, Val([Field1])) + IIF([Field2] Is Null,
0, Val([Field2])) + IIF([Field3] Is Null, 0, Val([Field3]))

--
Build a little, test a little.

Greetings all,
Here is my issue.
I have 3 fields containing several rows of data retrieved and calculated
from its Table. The query responds with several empty cells obviously no data
to capture.
When trying to sum up the 3 fields however if one of the three cells in that
record is missing data (null) then the ending sum is blank actually bypassing
the other two cells where data does reside.

I tried using the "nz" method. And it did return zeros as it should.
However, my sum column instead of adding the records concatenated the data.

Question: How do I add the 3 fields that contain random null cells so that
my sum column reports the correct sum for each record?

Thanks so much for your expertise!

Rick