Nz function???

[Tracy]

Heeeelp, my Nz function won't work as I expected with enormous consequences for all my work!

I have a field on a report called txtPrice. THen in my report code I have the following

If Nz(Me!txtPrice,0) > 0 then

msgbox "hello

End If

0 is not greater than 0, so I was not expecting a msg to appear, but it does.

Surely if Me!txtPrice is empty, and I use Nz, specifying that it return a zero, the stetment should evaluate properly? If I evaluate whether it's less than zero it works fine.

The only way round I can see is to say the following:

If Nz(Me!txtPrice)<> "" AND Nz(Me!txtPrice) > 0 then....

but this defeats the whole point of having the Nz function.

Any ideas would be gratefullt appreciated as I'm tearing my hair out.

 

[mission2java_78]

if(IIF(IsNull(Me.txtQuantity.Value), 0, Me.txtQuantity.Value)) > 0

Jon

 

[Rich]

Since your only looking for values greater than zero
If Me.TextPrice >0 Then
Do something
Nz seems redundant here anyway

 

[Tracy]

If I don't use Nz and Me.txtPrice = null, then it will error.

mission2java_78 : the code you give makes sense, but this is the job the Nz function should do. In the help it gives the same example, but says that you can then use Nz to simplify it.

It's a bit of a risky funtion to use if you ask me. If anyone know whay it won't work, please let me now!

 

[mission2java_78]

:-|

If the IIF works just use it. I don't know if Nz is friendly with nulls that is why I didnt think Rich's method would work..but I wasn't sure.

Jon

 

[harra]

Tracy,

That's funny. I can't reproduce the problem you are having. I have put a similar function in one of my databases and I don't get the message box unless the value is greater than 0. If the field is NULL then no messagebox.

This may be a stupid question, but have you tried stepping through your code and seeing what is being interpreted as the value of Me!txtPrice (you can do this by hovering your mouse pointer over the Me!txtPrice when the line is highlighted yellow). You might be having a situation where Me!txtPrice really isn't NULL.

I don't know if Nz is friendly with nulls

The NZ function is designed to recognize nulls and substitute a user-defined value, so I'd assume that it was friendly with nulls

 

[mission2java_78]

Ok...
I still don't see the big deal..its not like their is some efficiency using the Nz as opposed to the IIF.

jon

 

[harra]

I am the last person to talk to about whether or not something is efficient from a performance standpoint. I tend to rely on the findings/research of other Access Guru's I trust. For example, it is well known that the Domain Aggregate functions (i.e. DCOUNT, DLOOKUP, etc) are a bit poor when it comes to performace. When it comes to the NZ() function, I haven't seen any of my "resources" devote any time to determining if it or the equivalent IIF function is a better performer.

Having stated that, I think the big deal is that the NZ() function is much more efficient from a coding standpoint. You just pass it 2 parameters (the value you want to check for NULL and the value you want to use if NULL) and that is it. Over the years I have found that people find the IIF statement to be very confusing to understand and use. Don't ask me why, but I have had to fix many of those stupid things in people's spreadsheets and Access databases

 

[neileg]

harra, Tracey,

I can't reproduce the problem either.

I also agree that using Nz() produces more readable code even if there is no performance issue.

 

[mission2java_78]

Do end users see code?

Jon

 

[Shadez]

End users see what you want them to see..........

 

[Mile-O]

As if I'm trying to prove that I have no life, here's a quick database I've made to evaluate the performance of both the IIf() function and the Nz() function.

It returns the time taken to perform both functions 100,000 times consecutively and then tells you which one is faster and by what percentage.

 

[mission2java_78]

As if I'm trying to prove that I have no life, here's a quick database I've made to evaluate the performance of both the IIf() function and the Nz() function.

It returns the time taken to perform both functions 100,000 times consecutively and then tells you which one is faster and by what percentage.

Of course IIF is going to be slower...there is an additional function call to the IsNull function. My point being the performance hit isn't noticeable.

Jon

 

[Mile-O]

Even without the IsNull() function, the times are similar.

I know the time is minimal I just wanted to see.

 

[neileg]

Of course users don't see code, but I do! I struggle to unbderstand what I coded this morning, never mind reviewing code that is a few months old. Anything that aids comprehension is worth having.

 

[mission2java_78]

wow

if(IIF(IsNull(Me.txtQuantity.Value), 0, Me.txtQuantity.Value)) > 0

very complicated...

you haven't seen complicated code where pointers are pointing to rows of pointers in C++ all pointing to various data types.

Come on people this is Nz vs an if condition, not brain surgery.
Jon

 

[Pat Hartman]

The Nz() function should work as you coded it. Try stopping the code at that point and printing the contents of the price field. The only change I would make is to use the Dot (.) syntax rather than the Bang (!) syntax.

If Nz(Me.txtPrice,0) > 0 then

I suspect that the code is not actually being reached in which case the IIf() solution won't work any better.

 

[Shadez]

LOL


Dont confuse them mission2java, nOObs dont know what pointers are


But yes i have to agree iif is easy...


Nz is the best access function ever.......

 

[mission2java_78]

What's a noob ?

Jon

 

[Shadez]

if you dont know what a nOOb is then you are one....




nOOb = Newbie = New to something = You