[AccessD] Nz function in Access 2002

MessageI actually started using nz a couple years ago. I expect it was after
A97 was released, but I still had a couple customers have problems. So, I
gave up on it and still use my old function.  If I ever run across the issue
again, I'll post it on the site,





 -----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Charlotte Foust
Sent: Friday, March 07, 2003 11:34 AM
To: accessd at databaseadvisors.com
Subject: RE: [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
        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/318a1737/attachment-0001.html>