This Access tutorial explains how to use Access INSTR Function in Expression and VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare.
Access INSTR Function
Access INSTR function is used to search a substring (part of the string) within a string (string is a text) and return the position of the first occurrence.
For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.
If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, number “0” will return.
INSTR is always used to test whether a string contains a substring, as always we just want to know “True” or “False” but not the actual position, so we can simply test whether the returned value is “0”.
Syntax of Access INSTR Function
InStr( [start], string, substring, [compare] )
[start is] optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.
string is the full string
substring is part of the string you want to search
[compare] is optional. It is the type of comparison to perform. It can be one of the following values:
|vbUseCompareOption||-1||Use setting of Option Compare statement defined at Module level. In the absence of any Compare Option, BinaryCompare is used (case sensitive)|
|vbBinaryCompare||0||Binary comparison, turn text into a sequence to compare. Since the sequence is case sensitive, you can compare text using this option if you want the comparison to be case sensitive, which means you cannot search “aaa” within “AAA”|
|vbTextCompare||1||Performs a case insensitive textual comparison.|
|vbDatabaseCompare||2||Performs a comparison based on information in your database (case insensitive),
it is the default value when you create a database.
Note that if you use [compare], you have to also define [start], otherwise Access mistakenly thinks your string is start.
Below are the error handling of invalid parameters.
|string1 is zero-length||0|
|string1 is Null||Null|
|string2 is zero-length||start|
|string2 is Null||Null|
|string2 is not found||0|
|string2 is found within string1||Position at which match is found|
|start > string2||0|
Example of Access INSTR Function
|INSTR(3,”123123″,”2″)||5||The first occurrence of “2” in “123123” starting from position 3 is at position 5|
|INSTR(“FINANCE”,”f”)||0||By default, comparison is case sensitive, therefore “f” cannot be found in “Finance” and return 0|
|INSTR(1, “FINANCE”, “f”, vbTextCompare)||1||vbTextCompare turns the comparison into case insensitive. Defining[compare] requires you to also define [start]|
Alternative of Access VBA INSTR Function – using Wildcard
In Access VBA, if you just want to check if a text contains specific substring, you can use wildcard to return TRUE or FALSE.
VBA supports the below three Wildcard characters. Note that Wildcard is case sensitive.
|*||Represents one or more characters (any character)||J* any text that starts with J
*J starts with any text but ends with J
*J* any text that has J in the middle
|?||Represents one character (any character)||J? 2 characters that start with J
?J 2 characters that end with J
?J? 3 characters with J in the middle
|~||Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ?||J~** any text that starts with J*
~**J any text that starts with * and ends with J
~?*~* any text that starts with ? and ends with *
The below example returns a TRUE Msgbox:
Public Sub example() If "123456" Like "*23*" Then MsgBox (True) Else: MsgBox (False) End If End Sub