Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access 2007 Nz Function

Posted on 2010-08-23
8
984 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

828 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