Populating a calculated field 2

[BeHank]

I have an Access table containing numeric fields Fld1, Fld2, Fld3, ... FldN.

In a select query containing these fields is a calculated field FldTotal:Fld1+Fld2+Fld3+...+FldN.

In the datasheet view the FldTotal will not calculate unless each field has a numeric value. A blank or empty field requies a zero for the calculation to take place.

An awkward workaround has been to use an update query to populate each cell with a zero.

Surely there is a better solution?

Any help appreciated.

 

[MajP]

Wrap each one in a null to zero function

fldTotal: nz([fld1],0) + nz([fld2],0)....

 

[dhookom]

BeHank,
Any time I see requests to calculate across fields rather than across records raises a red flag regarding your table structure. Are you sure (or even concerned) about your lack of normalization?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Apparently NOT!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}