Vb6 and Access NZ function


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Vb6 and Access NZ function

  1. #1
    Jim F Guest

    Vb6 and Access NZ function


    We are using VB6 and Access 97. Access has an NZ function.
    Is there a way to use this function or something similar in VB6
    when retrieving data.

    Thanks in advance

  2. #2
    Paul Clement Guest

    Re: Vb6 and Access NZ function

    On 19 Jul 2000 06:24:36 -0700, "Jim F" <jhfarrel@hewitt.com> wrote:


    We are using VB6 and Access 97. Access has an NZ function.
    Is there a way to use this function or something similar in VB6
    when retrieving data.

    No. Access specific functions are not available to Visual Basic when used within an Access QueryDef.

    In Visual Basic you need to handle this at the time of assignment or reference of the field. For
    text fields you simply append an empty string:

    TextVal = rs.Fields("FieldName") & ""


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  3. #3
    Anthony Jones Guest

    Re: Vb6 and Access NZ function

    This is I believe the current solution posted on the www.vb2themax.com
    site. It reproduces the exact Nz functionality.

    Public Function Nz(rvntValue, Optional rvntDef)

    If IsNull(rvntValue) Then
    If IsMissing(rvntDef) Then
    Nz = Empty
    Else
    Nz = rvntDef
    End If
    Else
    Nz = rvntValue
    End If

    End Function


    --
    Anthony Jones
    Secta Group Ltd




  4. #4
    JohnW Guest

    Re: Vb6 and Access NZ function


    Not quite. This will more closely mimick the functionality of Access' Nz
    function:

    Function Nz(ByVal V As Variant, Optional ByVal ValueIfNull As Variant) As
    Variant
    If Not IsNull(V) Then
    Nz = V
    Else
    If IsMissing(ValueIfNull) Then
    If VarType(V) = vbString Then
    Nz = ""
    Else
    Nz = 0
    End If
    Else
    V = ValueIfNull
    End If
    End If
    End Function


    JW

    "Anthony Jones" <yadayadayada@msn.com> wrote:
    >This is I believe the current solution posted on the www.vb2themax.com
    >site. It reproduces the exact Nz functionality.
    >
    >Public Function Nz(rvntValue, Optional rvntDef)
    >
    >If IsNull(rvntValue) Then
    > If IsMissing(rvntDef) Then
    > Nz = Empty
    > Else
    > Nz = rvntDef
    > End If
    >Else
    > Nz = rvntValue
    >End If
    >
    >End Function
    >
    >
    >--
    >Anthony Jones
    >Secta Group Ltd
    >
    >
    >



  5. #5
    Jim Edgar Guest

    Re: Vb6 and Access NZ function


    John --
    >
    >Not quite. This will more closely mimick the functionality of Access' Nz
    >function:
    >
    >Function Nz(ByVal V As Variant, Optional ByVal ValueIfNull As Variant) As
    >Variant
    > If Not IsNull(V) Then
    > Nz = V
    > Else
    > If IsMissing(ValueIfNull) Then
    > If VarType(V) = vbString Then


    I'm not sure the following line of code will ever
    execute. This 'if' statement will execute if
    V is Null so why do you check the VarType() of V here?
    Can it ever = vbString if it is Null? I think this
    function will always return 0 if V is null and
    ValueIfNull is missing.

    Jim Edgar

    > Nz = ""
    > Else
    > Nz = 0
    > End If
    > Else
    > V = ValueIfNull
    > End If
    > End If
    >End Function
    >
    >
    >JW
    >
    >"Anthony Jones" <yadayadayada@msn.com> wrote:
    >>This is I believe the current solution posted on the www.vb2themax.com
    >>site. It reproduces the exact Nz functionality.
    >>
    >>Public Function Nz(rvntValue, Optional rvntDef)
    >>
    >>If IsNull(rvntValue) Then
    >> If IsMissing(rvntDef) Then
    >> Nz = Empty
    >> Else
    >> Nz = rvntDef
    >> End If
    >>Else
    >> Nz = rvntValue
    >>End If
    >>
    >>End Function
    >>
    >>
    >>--
    >>Anthony Jones
    >>Secta Group Ltd
    >>
    >>
    >>

    >



  6. #6
    JohnW Guest

    Re: Vb6 and Access NZ function


    Yeah, you're right. Unfortunately the problem is the Nz function in Access
    returns a valid 0 or empty string depending on the data type of the variable
    to which it is being assigned. Wonder how it does that? Must be an Access
    extension of some kind.

    Another approximation that might work okay would be something like...

    Function Nz(ByVal v As Variant, Optional ByVal ValueIfNull As Variant) As
    Variant
    Dim f As Field
    If Not IsNull(v) Then
    Nz = v
    Else
    If IsMissing(ValueIfNull) Then
    If TypeName(v) = "Field" Then
    Set f = v
    If f.Type = dbText Or f.Type = dbMemo Or f.Type = dbChar
    Then
    Nz = ""
    Else
    Nz = 0
    End If
    Else
    Nz = 0
    End If
    Else
    v = ValueIfNull
    End If
    End If
    End Function

    ..but this will still not mimick the Nz function exactly, and will break
    if you ever use an ADO field instead of a DAO field.

    The important thing to be aware of is if you're porting your DAO code from
    Access to VB, you can't just blindly use the Nz function the same way as
    you did in Access, as it has slightly different behavior.

    I would suggest that anyone doing this NOT make the ValueIfNull parameter
    optional, and instead try to compile it and correct all the errors that it
    raises. This is the only way (it seems) to be sure this will work exactly
    the same way as your DAO code.

    JW

    "Jim Edgar" <djedgar@home.com> wrote:
    >
    >John --
    >>
    >>Not quite. This will more closely mimick the functionality of Access'

    Nz
    >>function:
    >>
    >>Function Nz(ByVal V As Variant, Optional ByVal ValueIfNull As Variant)

    As
    >>Variant
    >> If Not IsNull(V) Then
    >> Nz = V
    >> Else
    >> If IsMissing(ValueIfNull) Then
    >> If VarType(V) = vbString Then

    >
    >I'm not sure the following line of code will ever
    >execute. This 'if' statement will execute if
    >V is Null so why do you check the VarType() of V here?
    >Can it ever = vbString if it is Null? I think this
    >function will always return 0 if V is null and
    >ValueIfNull is missing.
    >
    >Jim Edgar
    >
    >> Nz = ""
    >> Else
    >> Nz = 0
    >> End If
    >> Else
    >> V = ValueIfNull
    >> End If
    >> End If
    >>End Function
    >>
    >>
    >>JW
    >>
    >>"Anthony Jones" <yadayadayada@msn.com> wrote:
    >>>This is I believe the current solution posted on the www.vb2themax.com
    >>>site. It reproduces the exact Nz functionality.
    >>>
    >>>Public Function Nz(rvntValue, Optional rvntDef)
    >>>
    >>>If IsNull(rvntValue) Then
    >>> If IsMissing(rvntDef) Then
    >>> Nz = Empty
    >>> Else
    >>> Nz = rvntDef
    >>> End If
    >>>Else
    >>> Nz = rvntValue
    >>>End If
    >>>
    >>>End Function
    >>>
    >>>
    >>>--
    >>>Anthony Jones
    >>>Secta Group Ltd
    >>>
    >>>
    >>>

    >>

    >



  7. #7
    JohnW Guest

    Re: Vb6 and Access NZ function


    Oh, never mind, I see where I'm being blind here. Thanks for the tip!

    JW

    "JohnW" <JohnW@PreviewSystems.com> wrote:
    >
    >Yeah, you're right. Unfortunately the problem is the Nz function in Access
    >returns a valid 0 or empty string depending on the data type of the variable
    >to which it is being assigned. Wonder how it does that? Must be an Access
    >extension of some kind.
    >
    >Another approximation that might work okay would be something like...
    >
    >Function Nz(ByVal v As Variant, Optional ByVal ValueIfNull As Variant) As
    >Variant
    > Dim f As Field
    > If Not IsNull(v) Then
    > Nz = v
    > Else
    > If IsMissing(ValueIfNull) Then
    > If TypeName(v) = "Field" Then
    > Set f = v
    > If f.Type = dbText Or f.Type = dbMemo Or f.Type = dbChar
    >Then
    > Nz = ""
    > Else
    > Nz = 0
    > End If
    > Else
    > Nz = 0
    > End If
    > Else
    > v = ValueIfNull
    > End If
    > End If
    >End Function
    >
    >..but this will still not mimick the Nz function exactly, and will break
    >if you ever use an ADO field instead of a DAO field.
    >
    >The important thing to be aware of is if you're porting your DAO code from
    >Access to VB, you can't just blindly use the Nz function the same way as
    >you did in Access, as it has slightly different behavior.
    >
    >I would suggest that anyone doing this NOT make the ValueIfNull parameter
    >optional, and instead try to compile it and correct all the errors that

    it
    >raises. This is the only way (it seems) to be sure this will work exactly
    >the same way as your DAO code.
    >
    >JW
    >
    >"Jim Edgar" <djedgar@home.com> wrote:
    >>
    >>John --
    >>>
    >>>Not quite. This will more closely mimick the functionality of Access'

    >Nz
    >>>function:
    >>>
    >>>Function Nz(ByVal V As Variant, Optional ByVal ValueIfNull As Variant)

    >As
    >>>Variant
    >>> If Not IsNull(V) Then
    >>> Nz = V
    >>> Else
    >>> If IsMissing(ValueIfNull) Then
    >>> If VarType(V) = vbString Then

    >>
    >>I'm not sure the following line of code will ever
    >>execute. This 'if' statement will execute if
    >>V is Null so why do you check the VarType() of V here?
    >>Can it ever = vbString if it is Null? I think this
    >>function will always return 0 if V is null and
    >>ValueIfNull is missing.
    >>
    >>Jim Edgar
    >>
    >>> Nz = ""
    >>> Else
    >>> Nz = 0
    >>> End If
    >>> Else
    >>> V = ValueIfNull
    >>> End If
    >>> End If
    >>>End Function
    >>>
    >>>
    >>>JW
    >>>
    >>>"Anthony Jones" <yadayadayada@msn.com> wrote:
    >>>>This is I believe the current solution posted on the www.vb2themax.com
    >>>>site. It reproduces the exact Nz functionality.
    >>>>
    >>>>Public Function Nz(rvntValue, Optional rvntDef)
    >>>>
    >>>>If IsNull(rvntValue) Then
    >>>> If IsMissing(rvntDef) Then
    >>>> Nz = Empty
    >>>> Else
    >>>> Nz = rvntDef
    >>>> End If
    >>>>Else
    >>>> Nz = rvntValue
    >>>>End If
    >>>>
    >>>>End Function
    >>>>
    >>>>
    >>>>--
    >>>>Anthony Jones
    >>>>Secta Group Ltd
    >>>>
    >>>>
    >>>>
    >>>

    >>

    >



  8. #8
    Jim Edgar Guest

    Re: Vb6 and Access NZ function


    JohnW --

    Just to beat this to death, you can compare the Nz() function
    written by Anthony Jones to the Access Nz() function. Both
    return an Empty value if arg1 is Null and arg2 is missing. VB will implicitly
    coerce (sp ?) the return value to suit the needs of the calling expression.
    Start a new project in VB and make a reference to Microsoft.Access. Paste
    the following into the default form and run it. The code has Anthony's Nz()
    function and compares it to the Access.Nz() function. One small mod I made
    to Anthony's code is to check to see if arg2 is null after you check for
    is missing. If arg2 is another database field that happens to contain a
    null then the function will fail.

    Jim Edgar

    Option Explicit

    Private Sub Form_Load()

    Dim ap As New Access.Application

    Debug.Print TypeName(Nz(Null))
    Debug.Print Nz(Null) = 0
    Debug.Print Nz(Null) = ""
    Debug.Print Nz(Null) = Empty
    Debug.Print IsEmpty(Nz(Null))
    Debug.Print CInt(Nz(Null))
    Debug.Print CStr(Nz(Null))

    Debug.Print ""

    Debug.Print TypeName(ap.Nz(Null))
    Debug.Print ap.Nz(Null) = 0
    Debug.Print ap.Nz(Null) = ""
    Debug.Print ap.Nz(Null) = Empty
    Debug.Print IsEmpty(ap.Nz(Null))
    Debug.Print CInt(ap.Nz(Null))
    Debug.Print CStr(ap.Nz(Null))

    Set ap = Nothing

    End Sub

    Function Nz(arg1, Optional arg2)
    If IsNull(arg1) Then
    If IsMissing(arg2) Then
    Nz = Empty
    Else
    ' Check to see if arg2 is null
    If IsNull(arg2) Then
    Nz = Empty
    Else
    Nz = arg2
    End If
    End If
    Else
    Nz = arg1
    End If
    End Function

  9. #9
    Anthony Jones Guest

    Re: Vb6 and Access NZ function

    John,

    Type the following into Access's debug window.

    ?VarType(Nz(Null))

    You'll notice it returns 0 I.e., the variant create by Nz has the VarType
    of empty.



    --
    Anthony Jones
    Secta Group Ltd




  10. #10
    Anthony Jones Guest

    Re: Vb6 and Access NZ function

    >>
    One small mod I madeto Anthony's code is to check to see if arg2 is null
    after you check for is missing. If arg2 is another database field that
    happens to contain a null then the function will fail.
    <<

    Actually Jim, that is a spec change. The spec of Nz is to return the the
    first argument unless it is null else return the second. If a second
    argument is not supplied assume it to be an empty variant.

    Do this in Access:

    ?VarType(Nz(Null,Null))

    It returns 1, IOW it returns the null specified by the second argument.

    BTW, I can't accept credit for the code, I believe it can be found at
    www.vb2themax.com the authors naming being Heather McCaslin.


    --
    Anthony Jones
    Secta Group Ltd




  11. #11
    Jim Edgar Guest

    Re: Vb6 and Access NZ function

    Hi Anthony --

    > Actually Jim, that is a spec change. The spec of Nz is to return the the
    > first argument unless it is null else return the second. If a second
    > argument is not supplied assume it to be an empty variant.
    >
    > Do this in Access:
    >
    > ?VarType(Nz(Null,Null))
    >
    > It returns 1, IOW it returns the null specified by the second argument.
    >


    That is why I suggested testing the second argument for Null before
    assigning it as a return value. In the extremely unlikely event that both
    args are Null the following will generate an 'Invalid Use Of Null' error:

    Text1.Text = Nz(Null, Null)

    MS Access help file says that it returns either an empty string "" or
    a 0 if the first argument is Null and the second argument is missing.
    It actually returns an Empty variant which is coerced by the calling
    code into a string or integer. Your sample shows that it can also
    return a Null if both args are Null so the programmer needs to take
    that into consideration when using the return value. If he/she only
    wants to prevent the 'Invalid Use of Null' error from happening then
    returning an Empty variant might be an option if both args are Null.

    >BTW, I can't accept credit for the code, I believe it can be found at
    >www.vb2themax.com the authors naming being Heather McCaslin.


    Thanks for crediting Heather. A lot of code gets cut and pasted in
    these news groups without crediting the original author.

    Jim Edgar



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center