Forum Discussion
Using Nz. Is =0 necessary?
hello Experts,
I need to get this off my mind.
In order to properly test for Nz you only need to test like this if your field is a number:
Nz([Amount],0)
what's confusing to me is that I have seen examples where an =0 is added at the end like this:
Nz([Amount],0)=0
I have used both examples above in the following expression and I get different answers between both of them.
this one returns the [Amount], which I dont want:
=IIf((Nz([Amount],0)=0)<0,[Amount]*-1,[Amount])
this one returns what I want (the [Amount]*-1):
=IIf(Nz([Amount],0)<0,[Amount]*-1,[Amount])
Is =0 necessary if you are only testing for Nulls and want to change the Null to a 0 in the query? I dont understand the significance of the =0 part & if its necessary.
thank you
- Nz([Expr1], [Expr2]) function, simly means:
If the Value in [Expr1] is Null then Substitute [Expr2] as Return value, otherwise return the Value of [Expr1].
If you omit [Expr2] then the Default (which is a Null String "") will be returned.
5 Replies
This expression returns a boolean -- true or false
Nz([Amount],0)=0
What that means, in words, is this. If the value of Amount is Null, use 0, but if the value of Amount is not Null (i.e. it is a value which could include 0 because 0 itself is not null). Compare that result to 0.
If 0 = 0 then the result is true, otherwise it is false
Nz([Amount],0) returns a non-null value that could be any possible value from your Amount field, including 0 because, again 0 is not null.
In one case, then the answer is "True" or "False"
In the other case, the answer is any non-null number. e.g 1, 10,000, 22, -234, or 0
Does that help?
- thanks George.
One follow up:
<Nz([Amount],0) returns a non-null value that could be any possible value from your Amount field, including 0 because, again 0 is not null.
==>in the case of Null then it would return 0, correct?- Nz([Expr1], [Expr2]) function, simly means:
If the Value in [Expr1] is Null then Substitute [Expr2] as Return value, otherwise return the Value of [Expr1].
If you omit [Expr2] then the Default (which is a Null String "") will be returned.
