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

  • Thread starter Thread starter Laura
  • Start date Start date
L

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!
 
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
 
Do it like this (untested).

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


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Back
Top