how to add null fields?

E

emily

I am creating a query , and I need to add a number of fields to each other.
Some of the fields have data that are null. When the data is null, I cannot
create an expression to sum five fields for that record. The new expression
for that record becomes null. So if I have an expression with
70+45+21+null+16...how can I make the answer 157 and not null?????
 
R

rowiga

Use Null Zero:

Nz([Field1]+[Field2]+[Field3]+[Field4],0)

If any of the fields are Null they're treated as zero
 
D

Douglas J. Steele

However, that will mean that the answer will be 0 if any of the fields are
Null, which isn't what Emily wants.

You need

Nz([Field1], 0) + Nz([Field2], 0) + Nz([Field3], 0) + Nz([Field4], 0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



rowiga said:
Use Null Zero:

Nz([Field1]+[Field2]+[Field3]+[Field4],0)

If any of the fields are Null they're treated as zero

emily said:
I am creating a query , and I need to add a number of fields to each other.
Some of the fields have data that are null. When the data is null, I cannot
create an expression to sum five fields for that record. The new expression
for that record becomes null. So if I have an expression with
70+45+21+null+16...how can I make the answer 157 and not null?????
Click to expand...
Click to expand...
 
R

rowiga

Very true Doug....sorry for the misguided reply.

Douglas J. Steele said:
However, that will mean that the answer will be 0 if any of the fields are
Null, which isn't what Emily wants.

You need

Nz([Field1], 0) + Nz([Field2], 0) + Nz([Field3], 0) + Nz([Field4], 0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



rowiga said:
Use Null Zero:

Nz([Field1]+[Field2]+[Field3]+[Field4],0)

If any of the fields are Null they're treated as zero

emily said:
I am creating a query , and I need to add a number of fields to each other.
Some of the fields have data that are null. When the data is null, I cannot
create an expression to sum five fields for that record. The new expression
for that record becomes null. So if I have an expression with
70+45+21+null+16...how can I make the answer 157 and not null?????
Click to expand...
Click to expand...
Click to expand...
 
Top