CODEWITHSUNDEEP
ms-accessms-access-2007vba
KSM
KSM

Reputation: 262

Comparison Logic

I have an If statement which I was assuming was comparing each value to each other. However it seems no matter what the values are (e.g. all values contain a count of 4) it goes to the else. Am I missing something in the If statement? 

 If rst![CountOfProvider] = rst![CountOfDelivery Type] = rst![CountOfBU Creator] = rst![CountOfOrigin] = rst![CountOfSub-Destination] = rst![CountOfDestination Zipcode] = rst![CountOfCity] = rst![CountOfState] = rst![CountOfCost Zone] = rst![CountOfRate] = rst![CountOfMarket Name] Then
        chk = False
    Else
        chk = True
    End If

Upvotes: 1

Views: 68

Answers (2)

Cor_Blimey
Cor_Blimey

Reputation: 3310

The computationally quickest way is to hard code the comparisons. The more extensible way is to test via a loop.

HansUp makes a good comment - you should be wary of potential null values and add in a handler to deal with them as desired (e.g. using Nz() in Access or IsNull() in any host environment)

'Method 1
    If rst![CountOfProvider] = rst![CountOfDelivery Type] And _
       rst![CountOfProvider] = rst![CountOfBU Creator] And _
       ...etc...Then
        chk = False
    Else
        chk = True
    End If

'Method 2
    chk = True
    For Each ele In Array(rst![CountOfDelivery Type], rst![CountOfBU Creator],...your others...)
        If ele <> rst![CountOfProvider] Then
            chk = False
            Exit For
        End If
    Next ele

Upvotes: 2

HansUp
HansUp

Reputation: 97131

VBA doesn't perform that sequence of comparisons as you seem to expect.

Consider this simpler example from the Immediate window ...

Debug.Print 2 = 2
True
Debug.Print 2 = 2 = 2
False

I'm uncertain how VBA handles those multiple equality comparisons, but suspect it may be testing the first and then comparing the result from that with the next ... sort of like this ...

Debug.Print (2 = 2) = 2
False

The first comparison returns True, which is the integer -1 ...

Debug.Print CInt(2 = 2)
-1

So that means the final comparison would be equivalent to this ...

Debug.Print -1 = 2

And naturally that returns False.

Upvotes: 2

Related Questions