Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1005
  • Last Modified:

Access 2007 Nz Function

My OS is Win exp prof and I use Access 2007 from time to time.  i have come across this Nz function a number of times, but have no idea what it is for or what it does.  Can any of Gurus give me a simple explanation. thank u.
0
jegajothy
Asked:
jegajothy
3 Solutions
 
Patrick MatthewsCommented:
Nz allows you to test a value, and return it if it is non-null, or return a substitute value if it is null.Thus:SELECT Nz(Foo, 0)FROM SomeTablewill return Foo if it is not null, or zero if it is null.
0
 
Jeffrey CoachmanMIS LiasonCommented:
It is also worth noting that this function is MS Access specific.

Many people who fall in love with this function in Access, presume that it will work in SQL Sercver, MySQL, Oracle, ...ect.
In these Server DB programs you will have to use Coalesce or IIF()
ex.: IIF(Isnull([SomeField]),0,[SomeField])

Some people are confused, (or happy) by the fact that a function called "Null To Zero" can actually be used to return anything you want.
for example:
NZ([Freight],365)  will return 365 if Freight is null.
;-)

For this reason, I tend to only use it when I need to convert nulls to zero.

Now the kicker here is knowing *When* you should convert Nulls to Zero.
Nulls are not necessarily bad, they just mean the absence of a value.
See Here for more info:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_1910-Understanding-Null-Empty-Blank-N-A-ZLS-Nothing-Missing.html

For example, the average of:
2,4,6,0,8, 23, and 10, ...is 6.42

The average of:
2,4,6,Null,8,23 and 10, ...is 8.833

In other words some people will say this:
Sales:
Oct.,=Null, Null to zero=0
Nov.,=100,
Dec.,=Null, Null to Zero=0

So the average here would end up being 33.333.     (0+100+0)/3

If you did the same average and left the Nulls as nulls the result would be: 100/1=100
...Because nulls are not included in the calculation.
(But with only only 1 month of sales, is the average for the quarter really 100?)

So just be careful when you use it,

;-)

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Correction:
The average of:
2,4,6,0,8, 23, and 10, ...is 7.57

(I think...)
;-)

Jeff
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jeffrey CoachmanMIS LiasonCommented:
matthewspatrick,

Feel free to "Clarify" (the nice way of saying "Correct") anything I have stated above...
;-)

Jeff
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Dropping back just a tad (didn't read through everything so if someone said this already,sorry).
In database systems, a zero length string ("") and a zero (0) are both considered values in a field.   A NULL in a field is used to indicate the absence of any value.   For example, if I were recording temperatures, a zero is valid.  It does not indicate that a value was not recorded.  A NULL would.
NZ() originally converted a NULL to a zero since a NULL included in a math operation ([FieldA] + [FieldB]) won't work.  But NZ() includes a second parameter, which represents the value to return if a NULL is found.
HTH,
JimD.
0
 
jegajothyretiredAuthor Commented:
thank u to everyone who contributed.
0
 
Patrick MatthewsCommented:
Jeff,I see nothing to "clarify" except your arithmetic on the average :)I have no idea what the analogous function(s) would be in Oracle's PL/SQL.In SQL Server's T-SQL, I usually rely on COALESCE, which is even handier than Nz: I can pass any number of arguments to COALESCE, and it returns the first non-null value it finds (or null if every single one is null).  The T-SQL function that comes closest to Nz is IFNULL; it works exactly the same way Nz does.  T-SQL does not have an IIf function, although it does exist in MDX and Reporting Services, and if you use ActiveX scripting in DTS, VBScript implements the IIf function.  (Since I am a .Net ignoramus, I have no idea about SSIS.  My "Master" cert in VB.Net is a testament to the wonders of cross-posted questions.)For MySQL...my "Master" cert in that zone notwithstanding, I really do not know.  That cert is another artifact of cross-posting :)Patrick
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks Jim and Pat.

Yes Pat, the same goes for me, I only posted those as things I saw that could be substituted for NZ()

I'll send you an email about that average though...

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now