Access StrComp Function to Compare text (case sensitive comparison)

This Access tutorial explains how to use Access StrComp Function to compare text, both case sensitive and case insensitive comparison.

You may also want to read:

Access Case Sensitive Join Table (Inner Join, Left Join)

Access StrComp Function to Compare text (case sensitive comparison)

In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, if you type formula

="A"="a"

The formula returns TRUE. Vlookup also shows matching result even though one is CAP and one is not.

This case insensitive behavior is same in Microsoft Access. Even if you use A and a as a key field to JOIN Table, the key field comparison is also case insensitive. To perform case sensitive comparison in Expression, use Access Function StrComp. For case sensitive Table Join, I will explain in another post.

Syntax of Access StrComp Function

StrComp (  string1, string2  [, compare ] )
Argument Description
string1 Required. Any valid string expression.
string2 Required. Any valid string expression.
compare Optional. Specifies the type of string comparison. If the compareargument is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison.

Constant Value Description
vbUseCompareOption -1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Office Access 2007 only. Performs a comparison based on information in your database.

Access StrComp Function is closely related to VBA Instr Function, which also uses the compare argument in the function. Simply speaking, argument vbBinaryCompare allows case sensitive comparison, while vbTextCompare is case insensitive comparison.

There are 4 types of returned value

-1:  string1 > string2
0: string1 = string2
1: string 1 < string2
Null: either string1 or string2 is Null

Example of Access StrComp Function

For Textual Comparison (case insensitive)

Access Expression Result Explanation
StrComp(“Peter”,”PETER”,1) 0 string 1 = string2
StrComp(“Peter”,”Peter”,1) 0 string 1 = string2
StrComp(“PETER”,”Peter”,1) 0 string 1 = string2
 StrComp(“a”,”b”,1)  -1  string1 (a) > string2(b) in binary
 StrComp(“b”,”a”,1)  1  string1 (b) < string2 (a) in binary

For Binary Comparison (case sensitive)

Access Expression Result Explanation
StrComp(“Peter”,”PETER”,0) 1 string 1 < string2
StrComp(“Peter”,”Peter”,0) 0  string 1 = string2
StrComp(“PETER”,”Peter”,0) -1 string1 > string2
StrComp(“a”,”b”,0) -1 string1 (a) > string2(b) in binary
StrComp(“b”,”a”,0) 1 string1 (b) < string2 (a) in binary

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *