[AccessD] compare nulls

Mark,

While it is logically tricky to say Null = Null (even though Null is 
represented by a discrete value in VB), it is sometimes useful to ask if 
two variants have the same value. A slight distinction, but a useful one.

To that end, I wrote this VarComp routine some time back. This is my 
third try at this code. It has shortcomings: for performance reasons, it 
doesn't attempt even a shallow comparison of object properties or array 
contents. Obviously, there are no hard and fast rules here: one can bend 
this to their own ends.

' VarComp
' =======
' Compares two Variants. Returns True (same) or False (different),
' according to these rules:
'
'   Value1      Value1          Returns
'   ========================================================
'   Empty       Empty           True
'   Null        Null            True
'   Nothing     Nothing         True
'   Object      Object          Result of Typename comparison
'   Array       Array           Type and Bounds match only
'   Matching Value Types        Result of Value Comparison
'
'   Condition                   Returns
'  =========================================================
'   Any are Missing             False
'   Any One is Empty            False
'   Any One is Null             False
'   Any One is Nothing          False
'   Type Mismatch               False
'
' Notes:
'   String Comparisons are done using the TextCompare parameter
'   User Defined Types can't be passed as variants
'
Public Function VarComp(ByRef vValue1 As Variant, _
    ByRef vValue2 As Variant, _
    Optional ByVal TextCompare As VbCompareMethod = vbBinaryCompare) As 
Boolean
       
    Const CS_TypeString = "String"
   
    Static sType1 As String
    Static sType2 As String
       
    On Error GoTo HandleErr
       
    ' Init
    VarComp = False
   
    If IsMissing(vValue1) Or IsMissing(vValue2) Then Exit Function
   
    sType1 = TypeName(vValue1)
    sType2 = TypeName(vValue2)
   
    If (sType1 = sType2) Then
        ' Typenames match
        If IsObject(vValue1) Or IsEmpty(vValue1) Or IsNull(vValue1) Then
            ' Both are Same Object type, Nothing, Empty or Null
            VarComp = True
        ElseIf IsArray(vValue1) Then
            ' Both arrays of matching type
            VarComp = (LBound(vValue1) = LBound(vValue2)) And 
(UBound(vValue1) = UBound(vValue2))
        ElseIf sType1 = CS_TypeString Then
            ' Both are String
            VarComp = (StrComp(vValue1, vValue2, TextCompare) = 0)
        Else
            ' Value Type: not Object, Array, Empty, Null
            VarComp = (vValue1 = vValue2)
        End If
    End If
   
    Exit Function
   
HandleErr:
    Err.Raise Err.Number, "VarComp" & vbCrLf & Err.Source, Err.Description
End Function

> Hi Mark,
>
> Would this work?
>
> If IsNull(txt1) and IsNull(txt2) Then
> 	Do this
> Else
> 	Do that
> End If
>
>
> Dan Waters
>
> -----Original Message-----
> Subject: Re: [AccessD] compare nulls
>
> I think the explanation is that Null is nothing and nothing can't be
> equal to anything, not even another nothing. Or thereabouts. Perhaps
> some IS NULL functions can be added.
>
> GK
>   
>