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

  • Thread starter Thread starter Guitarzan
  • Start date Start date
G

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
 
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]))
 
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!

KARL DEWEY said:
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.


Guitarzan said:
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
Click to expand...
Click to expand...
 
Back
Top