Scared by Nulls not letting addition work--

R

Ralph

I have a table that I built through a query which used the totals feature to
group by and sum columns. I used an expression to calculate the sum of 3
columns, but it did not sum the information where any of the 3 columns had a
null value. I went back and re-ran the query and selected the columns
seperately for the new table, made a new update query to simply add the 3
columns. No luck. If ANY of the 3 columns had a null value and the other 2
had values, my answer was still null. If I update the nulls to a 0 value
the columns add OK. The columns are ALL DOUBLE so the data is "like". Is
there some setting in Access that is being used that I may need to change. I
use Access a LOT and have never seen this before and am scared I may have
sent data out that was not correct in other instances!!
 
F

fredg

I have a table that I built through a query which used the totals feature to
group by and sum columns. I used an expression to calculate the sum of 3
columns, but it did not sum the information where any of the 3 columns had a
null value. I went back and re-ran the query and selected the columns
seperately for the new table, made a new update query to simply add the 3
columns. No luck. If ANY of the 3 columns had a null value and the other 2
had values, my answer was still null. If I update the nulls to a 0 value
the columns add OK. The columns are ALL DOUBLE so the data is "like". Is
there some setting in Access that is being used that I may need to change. I
use Access a LOT and have never seen this before and am scared I may have
sent data out that was not correct in other instances!!
Click to expand...

Look up the Nz() function in VBA help.
=Nz([field1]) + Nz([field2]) + Nz(etc)
or
= Sum(Nz([labor])
 
T

Tim K

Hi Ralph,

You could set the default value to "0" (zero) in the properties of each
column, is that a possibility?

Tim K
 
Top