[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access 2007 Nz Function

Posted on 2010-08-23
8
Medium Priority
?
994 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

656 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