[AccessD] Nz function in Access 2002

Nz function in Access 2002Actually, 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
    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 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/60127402/attachment-0001.html>