Comparing Strings by Using Comparison Operators
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Since you're actually comparing ANSI values when you compare two strings, you can use the same comparison operators that you would use with numeric expressions—greater than (>), less than (<), equal to (=), and so on. In addition to these numeric comparison operators, you can also use the Like operator, which is specifically for use in comparing strings, including strings that contain wildcard characters.
Using Comparison Operators
When you use comparison operators such as the greater than (>) and less than (<) operators to compare two strings, the result you get depends on the string-comparison setting for the module. Consider the following example:
"vba" > "VBA"
If the string-comparison setting is Option Compare Binary, the comparison returns True.
When VBA performs a binary text comparison, it compares the binary values for each corresponding position in the string until it finds two that differ. In this example, the lowercase letter "v" corresponds to the ANSI value 118, while the uppercase letter "V" corresponds to the ANSI value 86. Since 118 is greater than 86, the comparison returns True.
Note For tables of ANSI values and the characters they represent, search the Visual Basic Reference Help index for "ANSI."
If the string-comparison setting is Option Compare Text, "vba" > "VBA" returns False, since the strings are equivalent apart from case.
In an Access database, if the string-comparison setting is Option Compare Database and the New Database Sort Order option is set to General (the default setting), the string comparison is case-insensitive and the example returns False.
Using the Like Operator
You can perform wildcard string comparisons by using the Like operator. The following table shows the wildcard characters supported by VBA.
| Wildcard | Represents | Example |
| * | Any number of characters | t* matches any word beginning with "t." |
| ? | Any single character | t??t matches any four-letter word beginning and ending with "t." |
| # | Any single digit (0–9) | 1#3 matches any three-digit number beginning with "1" and ending with "3." |
| [charlist] | Any single character in charlist | [a-z] matches any letter that falls between "a" and "z" (case-sensitivity depends on Option Compare setting). |
| [!charlist] | Any single character not in charlist | [!A-Z] excludes the uppercase alphabetic characters (case-sensitivity depends on Option Compare setting). |
You can use the Like operator to perform data validation or wildcard searches. For example, suppose you want to ensure that a user has entered a telephone number in the format nnn-nnn-nnnn. You can use the Like operator to check that the entry is valid, as the following procedure does:
Function ValidPhone(strPhone As String) As Boolean
' This procedure checks that the passed-in value is
' a valid, properly formatted telephone number.
ValidPhone = strPhone Like "###-###-####"
End Function
This procedure compares characters in a string to make sure that certain positions contain numeric characters. In order to return True, all characters must be digits between 0 and 9 or hyphens, and the hyphens must be present at the correct position in the string. This procedure is available in the modStrings module in VBA.mdb in the ODETools\V9\Samples\ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.