Confused about string comparison in VBA

I having some issue in a VBA (Excel) app I am currently working on.

At some point I have to retrieve some values in a database via SQL query and compare them a some values in a cell.

Something like (pseudo code)

if QueryResult = CellValue then DoSomething

Open in new window


Well my issue is that despite seemingly having equal value the "=" operator will not work as expected, ie. the comparison will return FALSE.

So I've gone one step further and wrote this function

Function sComp(prmString1 As String, prmString2 As String) As Boolean

Dim locResult As Boolean
Dim locStr As String
Dim i As Integer

    locResult = StrComp(prmString1, prmString2, vbTextCompare)
    locStr = "Comparing [" & prmString1 & "] " & Len(prmString1) & " with [" & prmString2 & "] " & Len(prmString2) & " result " & CStr(locResult)
    addLogEntry prmEntry:=locStr, prmClass:="clsSMSE", prmPK:=PK
    For i = 1 To Len(prmString1)
        addLogEntry prmEntry:=i & ":" & Asc(Mid(prmString1, i, 1))
    Next i
    For i = 1 To Len(prmString1)
        addLogEntry prmEntry:=i & ":" & Asc(Mid(prmString2, i, 1))
    Next i
        
    
    sComp = locResult

End Function

Open in new window

which uses the StrComp function and logs the details of what is happening

However, despite running a text compare (which should be more "lax") and actually having a byte by byte equality the stringy still don't match...

Log output
I muss confess that I am a little stomped at this stage... Any help most welcome
LVL 2
Alexandre TakacsCTOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Can you give some examples of non-matching strings?

Perhaps even upload a workbook with sample data.
0
Alexandre TakacsCTOAuthor Commented:
Well the example above is exactly that

The code tries to compare "Germany" (from SQL) with "Germany" (from Worksheet) and returns false.

Then I do a byte by byte dump off all characters in both strings and they match. Yet the compare does not !
0
Randy BristowSenior Systems AnalystCommented:
Are you using the function right?

(StrComp("abc", "ABC", vbTextCompare) = 0) » True
(StrComp("abc", "abc", vbTextCompare) = 0)  » True
(StrComp("abc", "ab", vbTextCompare) = 0)   » False

http://codevba.com/visual-basic-source-code/vb-string/checking_if_two_strings_are_equal.htm#.WstXZeSouUk
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
At the top of the module, look at the statement for:

 Option Compare

 What is it?   This statement is what controls how strings are compared.   The normal setting is "Database", but that sometimes will not give you what you want.

Jim.
0
Alexandre TakacsCTOAuthor Commented:
I thought that vbTextCompare passed to StrComp would avoid any doubt as of how string are handled
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, that is correct.   It would override the option statement (didn't catch that in the code).  

 Still something to be aware of though.   First thing I always go to when two matching strings are not equivalent.

Jim.
0
Randy BristowSenior Systems AnalystCommented:
The result will be -1, 0, 1, or null. Which value are you getting back?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So in place of this:

if QueryResult = CellValue then DoSomething

 Your are calling your  sComp() with those actual values?   If so, then that is a puzzler.....are you doing any conversions of the raw data, or are you doing the equals check directly on the values?

Jim.
0
Alexandre TakacsCTOAuthor Commented:
Your are calling your  sComp() with those actual values?   If so, then that is a puzzler.....are you doing any conversions of the raw data, or are you doing the equals check directly on the values?

Yes

I have since added a call to application.WorksheetFunction.Clean on both strings to no avail

The result will be -1, 0, 1, or null. Which value are you getting back?

Hmm let me check - I thought StrComp returns a boolean
0
Randy BristowSenior Systems AnalystCommented:
A Boolean false value is 0. This function returns 0 if you have a match (true).

string1 is less than string2 = -1
string1 is equal to string2 = 0
string1 is greater than  string2 =1
string1 or string2 is Null = Null

http://codevba.com/visual-basic-source-code/vb-string/checking_if_two_strings_are_equal.htm#.WstXZeSouUk
1
aikimarkCommented:
What is the value of the Option Compare statement in your General Declarations section?
0
Alexandre TakacsCTOAuthor Commented:
Ok my bad - I was not using StrComp correctly :(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.