Commonly there are 3 ways to do text comparison in MS Access.

1. Use InStr function
2. Use StrComp function
3. Option Compare Statement
   - Option Compare Binary
   - Option Compare Text
   - Option Compare Database
   

The two functions take precedence over the 3 Option Compare Statements if they both are used in a module. For example, if Option Compare Binary is specified for text sensitive comparison across anywhere in a VBA module, using StrComp in VBA code for case insensitive comparison would ignore the Option Compare Binary option, but for any other parts of the VBA module where StrComp is not used, Option Compare Binary still takes effect.

1. Use InStr function

InStr function returns the position number of the first occurrence of one string within another. If the string is not found, it returns 0.

The following example searches character F within string Flower. It returns 1.

Function test()
    Dim check As Long

    check = InStr(1, "Flower", "F", 0)
End Function

There are 4 arguments specified here.

1 - this sets the starting position for each search. Here we start at the 1st character of the string Flower.
Flower - this is the string being searched.
F - this is the string sought within string Flower.
0 - a case-sensitive (binary) search. You can also use VB constant instead of a number, see below.

Table below lists the VB constants and the equivalent numeric value. It instructs the type of string comparison INSTR performs.

2. Use the StrComp function

StrComp (string1, string2 [, compare ])

As its name suggests, the StrComp function compares two strings based on the type of comparison specified in the third argument.

• StrComp function returns 0 if the two strings are the same.
• Returns -1 if string1 is less than string2.
• Returns 1 if string1 is greater than string2.
• Returns Null if either string1 or string2 is Null.

Great or less is based on alphanumeric order. Numeric values are always less than alphabetical characters. Uppercase characters are less than lowercase characters. Lowercase English characters are less than Non-English characters, see below.

1 < 2 < 3 < 4 < 5 < 6 < 7 < 8 < 9 < 10 < 11 ... < A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

StrComp ("Geeks", "Geeks") Result: 0

StrComp ("Geek", "Gee", 0) Result: 1

StrComp ("geek", "Gee",1) Result: 1

StrComp ("Gee", "gee",0) Result: -1

StrComp ("À", "1.2",1) Result: 1

check = StrComp("a", "À", 1) Result: -1

StrComp ("3", "t",1) Result: -1

Use StrComp or InStr in a query as part of the WHERE clause

If text comparison is conducted between a text and the value from a text column in a table, you also can resort to StrComp or InStr to perform either case sensitive or non-case-sensitive searches.

SELECT * FROM Products WHERE StrComp(ProductName,'Chai',0)=0

Result: 1 record returned

SELECT * FROM Products WHERE InStr(1, ProductName,'chai',0)>0

Result: 0 records returned.

SELECT * FROM Products WHERE StrComp(ProductName,'Chai',1)=0

Result: 1 record returned

SELECT * FROM Products WHERE InStr(1, ProductName,'chai',1)>0

Result: 1 record returned

3. Option Compare Statement

The Option Compare Statement is used at module level to declare the default comparison method to use when string data is compared.

There are 3 types of Option Compare Statements.

• Option Compare Binary
• Option Compare Text
• Option Compare Database

3.1 Option Compare Binary

Option Compare Binary results in string case-sensitive string comparisons across anywhere in the VBA module where it's declared.

SELECT * FROM Products WHERE ProductName='chai'

Returns nothing because in Products table there is only 1 product named 'Chai'.

SELECT * FROM Products WHERE ProductName='Chai'

Returns 1 record.

3.2 Option Compare Text

Option Compare Text results in string case-insensitive string comparisons across anywhere in the VBA module where it's declared. It's based on the sort order determined by your system's locale.

SELECT * FROM Products WHERE ProductName='chai'

Returns 1 record.

SELECT * FROM Products WHERE ProductName='Chai'

Returns 1 record.

3.3 Option Compare Database

Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.

Happy Coding!


Copyright© GeeksEngine.com