Solved

Access 2007 Nz Function

Posted on 2010-08-23
8
979 Views
Last Modified: 2013-11-29
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
Comment
Question by:jegajothy
8 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 167 total points
ID: 33506654
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 33507990
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33508004
Correction:
The average of:
2,4,6,0,8, 23, and 10, ...is 7.57

(I think...)
;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33508011
matthewspatrick,

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

Jeff
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 166 total points
ID: 33510025
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
 

Author Closing Comment

by:jegajothy
ID: 33510204
thank u to everyone who contributed.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33510359
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33510973
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now