Microsoft Access Function Library: InStr()

InStr() is a vital function use to find elements in a string

Function: InStr()

Purpose: The InStr() function returns a number corresponding to the first location of a substring within a string.

Usage: InStr([start,] string, substring [,compare])

Start is an option argument specifying the location within the string to start searching. If you do not specify a starting location, Access uses the default value of 1. String is the string you wish to search within. Substr is the substring you wish to locate within String.

Compare is an optional argument specifying the type of comparison you wish to use. The possible values are:

  • vbUseCompareOption uses the Option Compare statement’s setting. This is the default setting.
  • vbBinaryCompare uses a binary comparison.
  • vbTextCompare uses a textual comparison.

Returns: Starting position of the substring within the searched string. If string is empty, it returns 0. If string is null, it returns null. If substring is empty, it returns the start value. If substring is null, it returns null. If there is no match, it returns 0.

Example

 InStr("chapple", "apple") = 3
 InStr("apple", "chapple") = 0
 InStr("","apple") = 0
 InStr("apple","") = 1
 InStr("apple",NULL) = NULL
 InStr(NULL,"apple") = NULL