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.

Read Also:  Access Templates Accounting Cash Book for Small Business

 
 

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

 
 

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

  • ms access instr function
  • ms access instr function example
  • access database syntax
  • access instr function with /
  • instring examples ms access
  • ms access instr funciton example
  • ms access parametised function
  • ms instr >0
Read Also:  Ms Access Remove Duplicates using Query or Ms Excel


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 VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top