Access Instr Function

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”.

INSTR Function can be used in Access Expression as well as VBA. For Excel spreadsheet, since there is no INSTR function, consider using Search Function or Find Function.

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:

Constant Value Description
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.

If InStr returns
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

VBA Code Result Explanation
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.

Wildcard Meaning Example
* 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

Outbound References

https://support.office.com/en-us/article/InStr-Function-85D3392C-3B1C-4232-BB18-77CD0CB8A55B

 

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 *