Wed. Feb 12th, 2025
Ms Access Instr Function Syntax and Use
Ms Access Instr Function Syntax and Use

The tutorial today will discuss about Ms Access Instr function and how to use it. The Instr function is purposed for searching the substring in the string and return the position of the first occurrence of one string within another.

The Syntax for Ms Access Instr

function is:
InStr( [start], string1, string2, [compare] )

 

Parameters

[start]: is the expression to set the starting position for each search. It is optional parameter, if omitted the search by default will be start with the first character postions.

String1: the full text that going to be searched

String2: the string or substring that being to be sought.

[compare]: determine the type comparison for the string. The value for [compare] parameter can be looked in this table.

Constant Value Description
vbUseCompareOption -1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare Performs a binary comparison, which turn text into a sequence to compare. The sequence is case sensitive.
vbTextCompare 1 Performs a case insensitive textual comparison.
vbDatabaseCompare 2 Performs a comparison based on information in your Access database (case insensitive).

Note: [compare] is optional, but if it used, you have to use the [start] parameter too.

 
 

Result

# Condition Returns
1. string1 is zero-length
2. string1 is Null Null
3. string2 is zero-length start
4. string2 is Null Null
5. string2 is not found
6. string2 is found within string1 Position at which match is found
7. start > string2
Read Also:  MS Access Substring Function

 
 

Examples

1. InStr (“Access Templates dot com”, “A”) or InStr (1, “Access Templates dot com”, “a”)

Result: 1

2. InStr (1, “Access Templates dot com”, “t”, 0)

Result: 14

Because using the vbBinaryCompare, which make the function is case sensitive.

3. InStr (5, “Access Templates dot com”, “A”)

Result: 13

Because it is started in 5th position.

4. InStr (“Access Templates dot com”, “dot”)

Result: 18

5. InStr(“Access Templates dot com”, “x”)

Result: 0

No found string2 in string1, see result condition #5.

Example in Query:

Example in VBA code:

Dim RackPosition As Integer

RackPosition = InStr (10, “Access Templates dot com”, “t”)

In VBA code, you only have to add the arguments for determine the Data Type. Normally the Instr function return the data type as Long.

Related Access Database

  • access instr function