InStr Function

See Also1MYA8C3                                               ExampleVFRJJC>Low

Returns the position of the first occurrence of one string within another string.

Syntax 1

InStr( [start,] strexpr1, strexpr2 )

Syntax 2

 InStr(start, strexpr1, strexpr2 [, compare] )

Remarks

The InStr function has these parts:

Part                 Description

start                 Numeric expression71RISN that sets the starting position for each search; start must be between 1 and approximately 65,535.  If start is omitted, the search of strexpr1 begins at the first character position.  The start argument is not optional if the compare argument is specified.

strexpr1            String expression1330R89 being searched.

strexpr2            String expression being sought.

compare           Specifies the string-comparison0QKI7U method.  The argument compare can be omitted; it can be 0 or 1; or it can be the value of the CollatingOrder property of a Field object.  If compare is omitted, the module default string-comparison method is used.  Unless you use Option Compare Text to change the default comparison method, the default is Option Compare Binary.

                        If compare is 0, string comparison is case-sensitive; so, for example, "M" doesn't match "m".  If compare is 1, string comparison is not case-sensitive; so, for example, "M" matches "m".

                        If you want to sort or compare values from a Database, Snapshot or Dynaset in the same way the Database would, you must provide the CollatingOrder property of the Field object.

If strexpr2 is found within strexpr1, InStr returns the position at which the match was found.  If strexpr2 is zero-length, start is returned.  If start is greater than strexpr2, strexpr1 is zero-length, or strexpr2 can't be found, InStr returns 0.

If either string expression is Null1DDW7C0, the function returns a Null.  If start or compare is Null, an error occurs.

See Also

Option Compare Statement1EF8XDE

StrComp Function4EP49UH

InStr Function Example

The example uses InStr to return the position of a character within a string.  To try this example, paste the code into the Declarations section of a form.  Then press F5 and click the form.

Sub Form_Click ()
  Dim Alphabet, LetterNum, Msg, UsrInput       ' Declare variables.
  Alphabet ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  Msg = "Please enter a single letter of the alphabet."
  UsrInput = UCase(InputBox(Msg))              ' Get user input.
  If Len(UsrInput) Then
    LetterNum = InStr(Alphabet, UsrInput)      ' Get position.
    If LetterNum <> 0 Then
      Msg = "Your letter is number " & LetterNum
      Msg = Msg  & " of " & Len(Alphabet) & "."
    Else
      Msg = "You didn't enter a letter of the alphabet!"
    End If
  Else
    Msg = "You didn't enter anything."
  End If
  MsgBox Msg                                   ' Display message.
End Sub