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] )



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

Read Also:  How to Fix MS Access Subscript Out of Range Error



# 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



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.

Access Database Tags: #microsoft access instr command #ms access abfrage instr #ms access expression builder instr #ms access expression instr #ms access formula instr #ms access instr backwards #ms access instr command #ms access instr function #ms access instr function wildcard #ms access invalid procedure call instr #ms access mid and instr

Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select
MS Access Tab Control in Form Tables or Subform
MS Access Tab Control in Form Tables or Subform
Access Tab Control is mainly used in
MS Access Left Function in SQL Query and VBA Code
MS Access Left Function in SQL Query and VBA Code
Ms Access Left Join function or sometimes

Must read×