Avoiding Null values and using 0 instead

[Frank M.]

I have an append query (INSERT INTO) that calculates
totals by subqueries and insert the result into a summary
table. Now, the sub query returns NULL when there are no
records to be summarized. In itself this is fine.
However, in a later update query I use the summary values
in further calculations, and it seems that when you add
values together the result will be NULL if just one of
the values in the expression is NULL, so I need to have a
zero instead of NULL.

I have thought about an extra update query to be run
where NULL values are replaced by 0. If it was just one
field, it would be easy to have an update query where the
criteria would be NULL value in the field. However, I
have quite a lot of summary fields, and having an extra
query for all of them will just make the operation
complicated and increase the probability for errors.

Is there a way to replace NULL in a number of fields in
record in one go, e.g. a conditional assignment of a
value to the field (i.e. if it is NULL set it to 0,
otherwise leave it as it is)?
Or is there any other way to handle the problem?


Regards,

Frank M.

 

[Frank M.]

I have found a solution. Using the Nz function to have a
0 returned instead of NULL, e.g.

SumField1 = Nz(EXPRESSION, 0)

Will set SumField1 to zero if the expression calculates
to NULL.

Frank M.

 

[Ken Snell]

Check out the Nz function. It allows you to substitute a different value for
a Null in an expression:

For example, the following expression will use 0 as the value of field
FieldName if the value of FieldName is Null:

Answer = 14 + Nz([FieldName], 0)