IIf statement in query, Null value change to "0"

[Laura]

I have two fileds (OT and DT) that I am trying to add together in a third
field in a query. Access will not let me do so if one of the values is null.
Therefore, I am trying to change the value to "0" if the field is null.

I have come up with the following two expressions:
IIf(IsNull([OT]),"0",[OT])
and
IIf(IsNull([DT]),"0",[DT])

However when I use the espressions in criteria, the null values do not
change to "0".

Any suggestions? Is there a way to get the values of the two fields added
together without changing the null fields to "0" in the original table?

Thank you!

 

[John Spencer]

You would not use the expressions in criteria. You would use them as a
calculated column (field) in a query.

Field: IIF(OT is Null,0,OT) + IIF(DT is Null,0,DT)

Or you can use the Nz function

Field: Nz(OT,0) + Nz(DT,0)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeanette Cunningham]

Do it like this (untested).

Expr1:Nz(OT,0) + Nz(DT,0)


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia