Update Query

[Carl Gross]

I have been trying to add multiple fields together to come up with a result
for one field.

It seems to work with two fields, but when I add the third field (of 6 or
seven), It doesn't. Why?

 

[John Spencer]

Probably because NULL (blank) values propagate. If any one value in the
expression is null then you will get null returned.

NZ(FieldA,0) + Nz(FieldB,0) + Nz(FieldC,0)

Using NZ forces the Null value to zero, so that you don't have the problem of
a null value in any one field making the calculation return null.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[fredg]

I have been trying to add multiple fields together to come up with a result
for one field.

It seems to work with two fields, but when I add the third field (of 6 or
seven), It doesn't. Why?

Your subject line indicates you want this for an update query which
would save the calculated value in a table.
John Spencer indicated a possible reason why your query 'does not
work', (whatever that means).

However, even if it did work, calculated values should not be stored
in a table. Saving calculated data not only wastes space but can lead
to erroneous data if one of the components of the calculation has been
changed.

Any time you need the calculated result, re-calculate it, using John's
expression.