You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
The VBA language is not, by default, case-sensitive. A statement such as
CBool("XYZ" = "xyz")
will always return True. You might come across this issue in cases such as the following:
If strOne = strTwo Then
' Perform some operation here
End If
Consider what happens if it's important to know that the strings differ only by case. By default, VBA will never report a difference in the strings based only on the case applied to the string variables.
One easy fix is to use the InStrB ("in string byte") function, which considers the strings based on byte value (where "x" is different than "X"). By contrast, the InStr function considers just character value ("x" is the same as "X").
The transformed If statement becomes:
If CBool(InStrB(strOne, strTwo)) Then...
This statement takes the output of InStrB() and converting it to a Boolean (True or False) expression. When InStrB returns 0 (no match found), CBool converts the expression's value to False. If InStrB finds the strings match (by case and by character) the expression evaluates to True.
You'll find that using the InStrB() function is much faster and easier than writing a VBA function that parses each string and does a binary or ASCII comparison on each character in both strings.
Comments: (7) Collapse
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?
tolmarc,
Sorry but we aren't talking about 2010 yet.
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.)
Comments: (loading) Collapse