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

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
 
K

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

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!

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top