Converting Null to Zero

[JeffreyE]

In a a query with a series of number fields, I have a mathematical expression that detects a variance if this equation does not add up to zero. If however one of those fields is left blank, its null value results in a null value for the expression. I have tried using the following in the criteria line for those number fields:

IIf(IsNull([x]),0,[x]), where x is the field name.

I have read about the Nz function but do not understand enough about building code to know how to use it.

Thanks for your help in advance.

Jeffrey Embrey

 

[MichaelRed]

The Nz is no more difficult to use than the IsNull function you are already familiar with. Pull up the Help fo each and compare them.

From the Help on Nz:

varResult = IIf(varTemp > 50, "High", "Low&quot

In the next example, the Nz function provides the same functionality as the first expression, and the desired result is achieved in one step rather than two.

varResult = IIf(Nz(varFreight) > 50, "High", "Low&quot


varResult = IIf(IsNull(varFreight), "No Freight Charge", varFreight)

In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.

varResult = Nz(varFreight, "No Freight Charge&quot


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JeffreyE]

Thanks very much for your quick response. My main problem was my lack of knowledge on how and where to plug in one of these functions. It was not working to plug it into the criteria lines of the number fields, but it works when I use the Nx function for each of the four values in the mathematical expression.

Thanks,

Jeffrey Embrey