If you are adding numbers from 31 fields, your data probably needs to
be normalized.
Having said that, you are correct in determining that adding 1 + 3 +
Null = Null. If you want 1 + 3 + Null = 4 you have to wrap your field
names in the NZ() function. This function takes to parameters, the
first one is an expression (could be a field) that when evaluated can
be tested to see if it is null. If the value is not null, then NZ()
just returns the value of the expression that was passed to it. If
the value of the expression is Null, then NZ will return a zero by
default, but can return an optional parameter as well. So
NZ([field1]) + NZ([field2]) + ... + NZ([field31]) should give you what
you want.
The other place that using NZ is really helpful is when working with
date fields. NZ([DateField], Date()) will return todays date if the
[DateField] is blank.
--
HTH
Dale Fye
Access (specifically 2000) defaults to missing when doing calculations
when
there is a missing field. In this case I don't want it to do that. I'm
adding the totals from a bunch of fields and don't want the sum to be
missing just because one of the 31 fields I'm adding are missing. How
can I
override that?
