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