Microsoft Access

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...

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?

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?

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.

@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.

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")

21

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

7

?Len("foobar fooBar")

13

Contrast this to InStr with comparison specified:

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

4

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

11

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.)