?
Solved

Access 2007 Nz Function

Posted on 2010-08-23
8
Medium Priority
?
991 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
[X]
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
8 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 668 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 668 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 664 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 93

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 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