MS Access Instr() Function

The Instr() function in MS Access searches for a substring within a string and returns the starting position of the first match. It's a very useful tool for locating specific parts of text data.

Instr(): Definition and Usage

Instr() is a helpful function for working with text. It searches a string for a specified substring and returns the position of the first occurrence of that substring. The search is case-insensitive by default. You can also optionally control where the search starts and the type of string comparison used.

Syntax

Syntax

InStr(start, string1, string2, compare)

Parameter Values

Parameter Description
start (Optional) The position to start searching from (default is 1).
string1 The string to search within. This is required.
string2 The substring to search for. This is required.
compare (Optional) The type of comparison:
  • -1: Uses the Option Compare setting.
  • 0: Binary comparison.
  • 1: Text comparison.
  • 2: Database-specific comparison.

Return Values

  • The starting position of the substring if found (a number greater than 0).
  • 0 if the substring is not found.
  • The value of the start parameter if string2 is zero-length.
  • 0 if string1 is zero-length.
  • NULL if string1 is NULL.
  • 0 if start is greater than the length of string1.

Example

Finding the Position of a Substring

This example searches for "t" within "Customer" and returns its position.

Syntax

SELECT InStr("Customer", "t") AS MatchPosition;
Output

5