Can't add properly in Expression

[Stevestan1]

I have a table with columns A, B, C, D. In column E, I have an expression that says Expn 1: [A] + [b] + [C] + [D]. Some records in columns A, B, C, D have null values. When I run the query, only records with non-null values in all four A-D columns will add. If there are one or more non-null values in any of the A-D columns, column E will also return as null. Any one know a solution for this?

[dsatino]

There's a bunch of ways to handle this, so you may want to wait for other responses as well, but here's one way:

IIF(nz([A],0)=0,0,[A])+ IIF(nz([b],0)=0,0,[b])+ .....

Basically, it tests for Null and if it finds a field to be null it simply uses 0.

[Mariostg]

Also, you may want to consider modifying your table design to default values to 0 when new records are created. So if only A and B are filled in, the others will have zeros.

[NeoPa]

I presume your column E is in a non-stated query rather than the table?

If so, Mario's advice is probably the most straightforward, but alternatively you could say :

E: Nz([A],0) + Nz([b],0) + Nz([C],0) + Nz([D],0)