Nz() Function

[Purdue15]

Im trying to prevent an error message when running a query. I am still receiving the error with this function what I'm I doing wrong?

Weight: Round(([CWeight]-Nz([NWeight],0))/[CWeight]*100,2)

* NWeight is the only possible Null value which is why I only used Nz on it, I tried this also
Weight: Round((Nz([CWeight],0)-Nz([NWeight],0))/Nz([CWeight],0)*100,2)

 

[MSAccessRookie]

Im trying to prevent an error message when running a query. I am still receiving the error with this function what I'm I doing wrong?

Weight: Round(([CWeight]-Nz([NWeight],0))/[CWeight]*100,2)

* NWeight is the only possible Null value which is why I only used Nz on it, I tried this also
Weight: Round((Nz([CWeight],0)-Nz([NWeight],0))/Nz([CWeight],0)*100,2)

It does not matter if CWeight is Null or not. If CWeight can have a value of 0 (Which could happen in the second example if CWeight was NULL), the equation will produce a divide by zero error.

To avoid this, consider using the IIf() Function to handle the error before it occurs. Something like the following might be a good start.

IIf((Nz([CWeight],0)=0), 0, Round(([CWeight]-Nz([NWeight],0))/[CWeight]*100,2))

-- Rookie

 

[Purdue15]

Ok so you got me thinking in the right direction

instead I'm using the IIf function in VBA

IIf(IsNull(Me.txtWeight), Me.txtWeight, Me.ccrtWeight)

this returns the value I want to if its NUll, although if its not null and I fill in the value it will still show me.ccrtWeight instead of me.txtWeight in the table. Why is that?

 

[MSAccessRookie]

Ok so you got me thinking in the right direction

instead I'm using the IIf function in VBA

IIf(IsNull(Me.txtWeight), Me.txtWeight, Me.ccrtWeight)

this returns the value I want to if its NUll, although if its not null and I fill in the value it will still show me.ccrtWeight instead of me.txtWeight in the table. Why is that?

If I understand what you want, then your IIf() appears to be reversed. The proper format is:

IIf({Condition}, {Value if Condition is True}, {Value if Condition is False})