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:  How to Update Access Database from Ms Excel

 
 

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

  • access instr function
  • instr function access
  • ms access instr
  • access query instr examples :
  • access instr
  • ms access query instr
  • access enter a school name as a parameter
  • what are the 4 file frames and their function in ms access
  • access instr function compare to table
  • access vba use variable in instr
Read Also:  MS Access Export To CSV


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

Powershell Query MS Access Database in Microsoft Windows Application
Powershell Query MS Access Database in Microsoft Windows Application
Powershell Access Database – Ms. Powershell is
Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function
Ms Access Weekday Function is purposed to


Must read×

Top