the NZ() function

[vba_php]

why is this so popular with access developers? Anyone have a take on this? I've been using Access for 25 years and not once have I used this function.

 

[theDBguy]

Either you've been very lucky or you're really good. If you're able to plan everything and control all situations, maybe you won't need to use it. However, in some cases, you may want to use it to force a different outcome. Or, at the very least, it can used as a shorthand If/Then statement/expression. Cheers!

 

[isladogs]

So how do you manage nulls?
Whether you use Nz or another approach such as Len or adding an empty string or using IsNull all developers have to use code to deal with nulls


Sent from my iPhone using Tapatalk

 

[June7]

Because it's short and sweet and does the job. I saw it as a gift from heaven when I first discovered it. Later I learned Excel would not work with a query that used Nz() unless Access library was activated.

I know it's a VBA function and causes some performance degradation in queries but not enough I can see difference.

 

[vba_php]

well these are good explanations you guys. I have used IIF() on many occassions, so i spose that's a substitute for NZ. I've also prolly logged hundreds of thousands of lines of VBA code at this point, and I'm positive I've also written substitutions for NZ in code although nothing specific comes to mind right off the bat.

 

[theDBguy]

well these are good explanations you guys. I have used IIF() on many occassions, so i spose that's a substitute for NZ. I've also prolly logged hundreds of thousands of lines of VBA code at this point, and I'm positive I've also written substitutions for NZ in code although nothing specific comes to mind right off the bat.

Hi. Here's a couple of potential examples where you might often see Nz() used. If you need to open a second form to show the details about the current record, the usual approach might look something like this:

DoCmd.OpenForm "FormName", , , "ID=" & Me.ID

Or, you might use DLookup() to look up a value and assign it to a variable. For example:

Dim UnitPrice As Currency
UnitPrice = DLookup("Price","tblProducts","ProductID=" & Me.ProductID)

In both of these cases, using the Nz() function can facilitate error handling/prevention when expected values were missing. Hope it makes sense...

 

[vba_php]

sure it makes sense. that's a good post dbGuy. Regarding dlookup() though, that function just sucks. everyone says it's incredibly slow too. I don't believe I've used that very much.

 

[theDBguy]

sure it makes sense. that's a good post dbGuy. Regarding dlookup() though, that function just sucks. everyone says it's incredibly slow too. I don't believe I've used that very much.

Well, almost anything available to us have strengths and weaknesses. You just need to know when to use which ones based on the situation. In addition, Access also gives us more than one way to accomplish the same thing. So, it sometimes boils down to personal taste. Just my 2 cents...

 

[isladogs]

I've done speed comparison tests on various items which are available both here and on my website

Nz is marginally faster than other methods of checking for nulls.

DLookup is also fast unless its being used on every record in a query (which is poor design anyway). Many years ago all domain functions were very slow but these have since been improved. If used wisely, they are no longer slow.

 

[vba_php]

what is everyone's opinion on the differences that come out between access versions? I think MS is horrid when it comes to this. If encountered numerous problems over the years with inconsistencies between access versions. for instance, when 2013 came out, I would constantly get this "file format not recognized" error after opening a pre-2013 database with access 2013, then opening it once with an earlier version of access, then opening it again with 2013. .accdb came out in 2007 and it hasn't changed extensions ever since. stuff like that shouldn't happen.

the data macros were also incredibly stupid to implement. I'm not generally a fan of access for these reasons. I've also encountered situations where access stops working for no reason (kind of like windows crashing for no reason). I worked at Rockwell Collins for a year a while back and they were using Access as their database files for their massive VB6 app and we would run into constant problems with access not capturing data that was sent to it via I/O or even other methods. At the time I left, they were in a transition to VB.NET. So i hope that served them better.

 

[pbaldy]

Why would you consider data macros stupid? They're a tool; like any other, they have their place. I haven't used them, but I've used SQL Server triggers, which data macros are very loosely equivalent to.

They've definitely had issues with updates fixing one thing and breaking another. That has been very frustrating for many.

 

[vba_php]

Why would you consider data macros stupid?

cuz i'm not a fan of messing with data at the table level.

 

[Galaxiom]

Nz() is handy to substitute another field or display something place of Nulls. However it is best avoided in Where clauses.

WHERE filedname Is Null OR fieldname = 0

should perform better than

WHERE Nz(fieldname,0) = 0

It was certainly the advice given many years ago by the bastions of Access users based on Is Null and equality being native SQL and Nz() being a function that needed to be applied to every record.

Colin (Isladogs) might have some tests that say otherwise.

 

[theDBguy]

cuz i'm not a fan of messing with data at the table level.

Data macros don't only help when a change is done at the table level. Let's say you have a form for the user to use; however, let's say you, as a developer, needed to run a batch query to do a one-time update to a lot of records at the same time. In this case, it might be good to have a data macro, if possible, because you're not using a form in this situation.

 

[vba_php]

WHERE Nz(fieldname,0) = 0

that doesn't even make sense! redundancy in there!

 

[isladogs]

Following on from Galaxiom's comments, these speed tests cover many of the above points about nulls, native Access vs VBA functions (and more): Optimise Queries

When I have time I'm also going to update these articles with additional information/evidence
Handling Nulls; HAVING vs WHERE

that doesn't even make sense! redundancy in there!

This comment was about setting the 'alternative' value for null using the Nz function.
It makes total sense. Which part don't you understand?

 

[June7]

If alternative value not provided for Nz() seems Access will assign one as circumstances dictate.
In immediate window:
?0=Nz(Null)
True
?""=Nz(Null)
True

Probably best practice to specify a value.

 

[theDBguy]

that doesn't even make sense! redundancy in there!

LOL. This goes back to your original question of why the Nz() function is so popular with access developers. To us, Access developers, that WHERE clause from Galaxiom makes perfect sense. You can actually replace 0 with any other value.

...WHERE Nz(Field,"Anything")="Anything"

 

[theDBguy]

Probably best practice to specify a value.

I actually agree and try to. I just purposely didn't do it in the earlier examples to keep the topic discussion brief and concentrated on whether to use it or not - and not necessarily on the how. Cheers!

 

[vba_php]

LOL. This goes back to your original question of why the Nz() function is so popular with access developers. To us, Access developers, that WHERE clause from Galaxiom makes perfect sense. You can actually replace 0 with any other value.

...WHERE Nz(Field,"Anything")="Anything"

maybe I'm more of a web developer than a database dev? soon i'll be developing my first mobile app for android. so before i know it my nickname will be "swifty". does anyone follow that joke?