Columns in query not totalling

[apex77]

I have created a query with 4 columns, LEAD, ADVANCE, CLOSE, TOTAL. Each
record in the Total column is to be a calculation of the contents of other 3
columns. The calculation is as follows:

Total: ([Lead]+[Advance]+[Close])
....and is an Expression

When there is data in ALL of the first 3 columns, the total is fine. For
example, in record 1, the LEAD amount is 4, the ADVANCE is 3 and the CLOSE is
2. This would put a 9 in the TOTAL field. One the other hand, if one of the 3
fields does not have data in it, the TOTAL field shows nothing. I have tried
formatting the columns as both currency and general. Thanks in advance for
the help.

 

[Jerry Whittle]

Use the NZ function to return a zero instead of a null.

Total: (NZ([Lead],0)+NZ([Advance],0)+NZ([Close]),0))

 

[apex77]

Exactly what I Needed! Thanks.

Use the NZ function to return a zero instead of a null.

Total: (NZ([Lead],0)+NZ([Advance],0)+NZ([Close]),0))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have created a query with 4 columns, LEAD, ADVANCE, CLOSE, TOTAL. Each
record in the Total column is to be a calculation of the contents of other 3
columns. The calculation is as follows:

Total: ([Lead]+[Advance]+[Close])
...and is an Expression

When there is data in ALL of the first 3 columns, the total is fine. For
example, in record 1, the LEAD amount is 4, the ADVANCE is 3 and the CLOSE is
2. This would put a 9 in the TOTAL field. One the other hand, if one of the 3
fields does not have data in it, the TOTAL field shows nothing. I have tried
formatting the columns as both currency and general. Thanks in advance for
the help.

 

[John W. Vinson]

I have created a query with 4 columns, LEAD, ADVANCE, CLOSE, TOTAL. Each
record in the Total column is to be a calculation of the contents of other 3
columns. The calculation is as follows:

Total: ([Lead]+[Advance]+[Close])
...and is an Expression

When there is data in ALL of the first 3 columns, the total is fine. For
example, in record 1, the LEAD amount is 4, the ADVANCE is 3 and the CLOSE is
2. This would put a 9 in the TOTAL field. One the other hand, if one of the 3
fields does not have data in it, the TOTAL field shows nothing. I have tried
formatting the columns as both currency and general. Thanks in advance for
the help.

The problem is that NULL means "unspecified", "Unknown", "undefined". The
value of any expression involving NULL is itself NULL, since 3 + <some unknown
value> is of necessity unknown.

Try

Total: NZ([Lead], 0) + NZ([Advance], 0) + NZ([Close], 0)

The NZ (null to zero) function will convert null values to 0 (or to some other
value if you put some other value in its second argument).