Case-Sensitive string comparisons

re: Case-Sensitive string comparisons

Thom Rose — Fri, 26 Jun 2009 23:53:18 GMT

Of course, doing just that does not tell you the two strings are the same. It only tells you if strTwo is contained in strOne. For example, if strTwo is "Thom" and strOne is "Thom Rose", CBool(InStrB(strOne, strTwo)) returns true. To find out if the strings are equal, the line should read:

If CBool(InStrB(strOne, strTwo)) And CBool(InStrB(strTwo, strOne)) Then...

re: Case-Sensitive string comparisons

Banana — Sat, 27 Jun 2009 00:50:12 GMT

Actually, one would use StrComp(strOne, strTwo, vbCompareBinary) which would return 0 if both strings are equal, -1 or 1 when either string are different and the case is sensitive.

That said, there is an interesting problem- we can easily solve the case sensitivity but not as easily the accent sensitivity.

Consider the comparison:

"Mueller" = "Müller"

In the default "General Sort Order" collation of a .mdb file, the comparison is false. However, if we create a new database using German Phonebook collation the comparison now evaluates to true. How would one achieve this comparison in a function short of putting the strings in a brand new database with the collation accordingly set?

re: Case-Sensitive string comparisons

tolmarc — Sat, 27 Jun 2009 01:57:27 GMT

Thanks for the tip.

by the way, could the Access team will share a bit more on the future of VBA in the 2010 version? will it be based on VB.net?

re: Case-Sensitive string comparisons

Clint Covington — Sat, 27 Jun 2009 03:06:13 GMT

tolmarc,

Sorry but we aren't talking about 2010 yet.

re: Case-Sensitive string comparisons

Wayne Phillips — Sat, 27 Jun 2009 10:42:16 GMT

Don't forget you can also put Option Compare Binary at the top of your code modules which then changes the way strings are compared inside those modules.

Then, CBool("XYZ" = "xyz") would evaluate to false.

However, I would usually use the StrComp method (as Banana suggests above) since it's more obvious that you are specifically comparing with case sensitivity.

re: Case-Sensitive string comparisons

Wayne Phillips — Sat, 27 Jun 2009 18:37:53 GMT

@Banana...

There is an undocumented feature of the StrComp function that may be useful in that scenario...

You can pass a locale ID as the third parameter to the StrComp function. So if we know that the German phonebook LCID has the constant value &H10407, we can just do this:

Const LOCALE_GERMAN_PHONEBOOK As Long = &H10407

StrComp("Mueller", "Müller", LOCALE_GERMAN_PHONEBOOK)

... which should return 0 (i.e. equal).

Unfortunately, in passing an LCID to StrComp means that the comparison is always case insensitive. If you need a case sensitive version when specifying a specific LCID, you'll need to use the CompareString API instead.

re: Case-Sensitive string comparisons

Banana — Sun, 28 Jun 2009 00:12:21 GMT

Wayne,

Very handy! Thanks for the tip.

WRT the InStrB, I think a note should be made that it may be comparing at the binary level, not at character, which can be confusing.

Consider the statement:

?InStrB("foobar fooBar","bar")

?Len("foobar fooBar")

Contrast this to InStr with comparison specified:

?InStr(1,"foobar fooBar", "Bar",vbTextCompare)

?InStr(1,"foobar fooBar", "Bar",vbBinaryCompare)

If we recall that the Access by default uses ucs2 (I think?) so each character are always two bytes wide, so a letter "A" is represented in this binary form:

00000000 01000001

When we did the InStrB, it's counting each bytes, not characters and returns the return where the starting byte of the set matches the input. Hence, the result of 21 and 7 in above InStrB represented the actual byte position while InStr using Binary comparsion (e.g. the set of characters as a group of bytes must match exactly the input's group of bytes, rather than merely comparing each byte against each byte)

(Note this is mostly guesswork based on my limited understanding of how characters & bytes are compared so if I'm incorrect, feel free to correct.)