Navigation:  Bronze Collection > Jul-1997 > Tip: Use StrComp to Make Case-Sensitive Comparisons

Paul Litwin

 

Have you ever had the need to make case-sensitive comparisons? In the past when I've had to make case-sensitive comparisons, I've created a comparison function and placed it in a module that contained an Option Compare Binary statement in its declarations section. While there's still nothing wrong with this approach, a smarter approach is to use the largely unknown built-in StrComp function. This function has been a part of Access (and the Access Basic/VBA language) since the days of Access 2.0.

 

Here's how it works: You pass StrComp the two strings you'd like to compare and an argument that tells it how to compare the strings. The third argument can be 0 (in Access 95 and Access 97, you can use the vbBinaryCompare constant) to request a binary or case-sensitive comparison, 1 (vbTextCompare) for a case-insensitive comparison, or 2 (vbDatabaseCompare) for a comparison governed by the database sort order.

 

StrComp obliges by returning one of the following values:

 

Return value          Description 

0           Strings are equivalent 

-1          First string comes alphabetically before the second 

1           First string comes alphabetically after the second 

Null          One or both of the strings are Null 

 

For example, the following function (which works in any version of Access since 2.0) will tell you if two strings are equal (counting differences in case), even when placed in a module that doesn't include an Option Compare Binary statement. It returns -1 (True) if the strings are equal; otherwise it returns 0 (False):

 

Function EqualStrings (str1 As String, _

 str2 As String) As Integer

    EqualStrings = (StrComp(str1, str2, 0) = 0)

End Function