<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Nz function in Access 2002</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2600.0" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff
size=2>Actually, from time to time I have run into a problem with NZ. Many, many
a year ago, before NZ existed, I wrote my own function and have found it
never to fail. </FONT></SPAN></DIV>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff
size=2>Whenever my customers have a problem with NZ, I send them this instead of
trying to debug their code or query. Always fixed the
problem</FONT></SPAN></DIV>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff size=2>That's
just my experience. Here's my code:</FONT></SPAN></DIV>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff
size=2>Function N2Z(anyValue As Variant) As
Double<BR>''********************************************************************************<BR>'' CDD
Procedure
Identification<BR>''--------------------------------------------------------------------------------<BR>''
FUNCTION:
N2Z<BR>''<BR>'' PURPOSE:
Used through the application Converts null and empty values to
zero<BR>''<BR>'' ARGUMENTS: anyValue -
any value to check<BR>''<BR>''
RETURNS:<BR>''<BR>''<BR>'' Date/AUTHOR: John
Skolits
03/14/97<BR>''********************************************************************************</FONT></SPAN></DIV>
<DIV><SPAN class=613115613-07032003><FONT face=Arial color=#0000ff size=2>On
Error GoTo N2Z_ERR</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial color=#0000ff
size=2></FONT><FONT face=Arial color=#0000ff size=2></FONT><BR><FONT face=Arial
color=#0000ff size=2>'*********BEGIN CODE HERE ********</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff
size=2> On Error GoTo
N2Z_ERR</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=613115613-07032003> </SPAN>If anyValue = "#Deleted" Then
anyValue = Null</FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial color=#0000ff
size=2></FONT><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial
color=#0000ff size=2></FONT><BR><FONT face=Arial color=#0000ff
size=2> If IsNull(anyValue) Or IsEmpty(anyValue)
Then<BR> N2Z =
CDbl(0)<BR> Else<BR>
N2Z = CDbl(anyValue)<BR> End If</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>N2Z_EXIT:</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial color=#0000ff
size=2></FONT><BR><FONT face=Arial color=#0000ff size=2>Exit
Function</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial color=#0000ff
size=2></FONT><BR><FONT face=Arial color=#0000ff size=2>N2Z_ERR:<BR><SPAN
class=613115613-07032003>
</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=613115613-07032003></SPAN>If Err = 13 Then Resume N2Z_EXIT</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2> If Err =
3021 Then<BR> MsgBox "You are trying to use the Null To Zero
function (N2Z) with no data. (Note: This message may appear multiple times.
Click OK till the message clears.)", , " Null To Zero Function
Error"<BR> Resume
N2Z_EXIT<BR> End
If<BR> <BR> If Err = 2427 Or Err = 2424 Or Err
= 63933 Then<BR> N2Z =
CDbl(0)<BR> Resume
N2Z_EXIT<BR> End If</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2> MsgBox
"ERROR CODE:" & Err & " DESC:" &
Error<BR> Resume N2Z_EXIT</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>End Function</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-admin@databaseadvisors.com
[mailto:accessd-admin@databaseadvisors.com]<B>On Behalf Of </B>William
Hindman<BR><B>Sent:</B> Thursday, March 06, 2003 3:27 PM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> Re: [AccessD] Nz function in
Access 2002<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>...this is a prime example of Microsoft geek
writing ...the reason I never buy anything from MS Press ...I'll be damned if
I can figure out what it is that the nz function doesn't quite perform "as
expected"? :(</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>...I use nz in several AXP apps and to date have
noticed nothing that I didn't expect ...but with this kind of crap KB, I'll be
certain to waste a lot of time the next time I use it, just futzing about
looking for some kind of error in the returned data :(((((</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>...I don't know whether to thank Charlotte for
pointing this out or mail her my dirty laundry instead :(</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV>William Hindman ...ok, I won't mail her my dirty laundry ...probably
upset my post lady :)</DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=cfoust@infostatsystems.com
href="mailto:cfoust@infostatsystems.com">Charlotte Foust</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=AccessD@databaseadvisors.com
href="mailto:AccessD@databaseadvisors.com">AccessD@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, March 06, 2003 1:56
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> [AccessD] Nz function in
Access 2002</DIV>
<DIV><BR></DIV><!-- Converted from text/rtf format -->
<P><FONT face="Comic Sans MS" size=2>I ran across an MSKB article today
</FONT><A
href="http://support.microsoft.com/default.aspx?scid=kb;en-us;295619"><U><FONT
face="Comic Sans MS" color=#0000ff
size=2>http://support.microsoft.com/default.aspx?scid=kb;en-us;295619</FONT></U></A><FONT
face="Comic Sans MS" size=2> that says the Nz function works in 2002 but it
may not work as expected! Say what?? It works, but it
doesn't?</FONT></P>
<P><FONT face="Comic Sans MS" size=2>Has anyone run into this? Nz
isn't always the most appropriate function, but I've never seen it fail, at
least not that I knew about. We use this a lot, and I'm concerned
about migrating our apps from 97 to 2002 and having a lot of code fall
over. I wondered if it could be the result of not passing in the
optional argument, but the article seemed rather vague to me. Does
anyone else have first-hand knowledge of the problem?</FONT></P>
<P><FONT face="Comic Sans MS" size=2>Charlotte Foust</FONT>
</P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>