Calculating a Null Value

[jlo]

I have two fields that I want to subtract from one another. However, there
will be times when the one field is null.

How can I get [field1] (500) -[field2] (blank) to give me 500?

 

[John Spencer]

Use the NZ function to force a value of zero.

[Field1] - Nz([Field2],0)

Or use the IIF function

IIF(IsNull([Field2]),[Field1],[Field1]-[Field2])

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

 

[jlo]

Thank you! That's exactly what I wanted.

Use the NZ function to force a value of zero.

[Field1] - Nz([Field2],0)

Or use the IIF function

IIF(IsNull([Field2]),[Field1],[Field1]-[Field2])

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

I have two fields that I want to subtract from one another. However, there
will be times when the one field is null.

How can I get [field1] (500) -[field2] (blank) to give me 500?