[AccessD] Nz function in Access 2002

Early version of Access had a sample of code for a function called
CNulls(), I think, in the help files.  You had to build your own Null
replacement function because Nz didn't exist until Access started using
VBA.  I stopped using it when Nz became available and have never had any
problems.
 
Charlotte Foust
 

	-----Original Message-----
	From: John Skolits [mailto:JSkolits at corporatedatadesign.com] 
	Sent: Friday, March 07, 2003 6:06 AM
	To: accessd at databaseadvisors.com
	Subject: RE: [AccessD] Nz function in Access 2002
	
	
	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. 
	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
	 
	That's just my experience.  Here's my code:
	 
	 
	Function N2Z(anyValue As Variant) As Double
	
''**********************************************************************
**********
	''                         CDD Procedure Identification
	
''----------------------------------------------------------------------
----------
	''    FUNCTION:          N2Z
	''
	''    PURPOSE:       Used through the application Converts null
and empty values to zero
	''
	''    ARGUMENTS:     anyValue - any value to check
	''
	''    RETURNS:
	''
	''
	''    Date/AUTHOR:   John Skolits 03/14/97
	
''**********************************************************************
**********
	On Error GoTo N2Z_ERR
	
	'*********BEGIN CODE HERE ********
	 
	        On Error GoTo N2Z_ERR
	 
	    If anyValue = "#Deleted" Then anyValue = Null
	 
	
	    If IsNull(anyValue) Or IsEmpty(anyValue) Then
	        N2Z = CDbl(0)
	    Else
	        N2Z = CDbl(anyValue)
	    End If
	 
	N2Z_EXIT:
	
	Exit Function
	
	N2Z_ERR:
	        
	If Err = 13 Then Resume N2Z_EXIT
	 
	     If Err = 3021 Then
	    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"
	        Resume N2Z_EXIT
	     End If
	 
	     If Err = 2427 Or Err = 2424 Or Err = 63933 Then
	        N2Z = CDbl(0)
	        Resume N2Z_EXIT
	     End If
	 
	      MsgBox "ERROR CODE:" & Err & "   DESC:" & Error
	      Resume N2Z_EXIT
	 
	End Function
	 
	 

		-----Original Message-----
		From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of William Hindman
		Sent: Thursday, March 06, 2003 3:27 PM
		To: accessd at databaseadvisors.com
		Subject: Re: [AccessD] Nz function in Access 2002
		
		
		...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"? :(
		 
		...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 :(((((
		 
		...I don't know whether to thank Charlotte for pointing
this out or mail her my dirty laundry instead :(
		 
		William Hindman ...ok, I won't mail her my dirty laundry
...probably upset my post lady :)

			----- Original Message ----- 
			From: Charlotte Foust
<mailto:cfoust at infostatsystems.com>  
			To: AccessD at databaseadvisors.com 
			Sent: Thursday, March 06, 2003 1:56 PM
			Subject: [AccessD] Nz function in Access 2002


			I ran across an MSKB article today
http://support.microsoft.com/default.aspx?scid=kb;en-us;295619
<http://support.microsoft.com/default.aspx?scid=kb;en-us;295619>  that
says the Nz function works in 2002 but it may not work as expected!  Say
what??   It works, but it doesn't?

			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?

			Charlotte Foust 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030307/6268b931/attachment-0001.html>