See Also Example
Returns the position of the first occurrence of one string within another string.
InStr( [start,] strexpr1, strexpr2 )
InStr(start, strexpr1, strexpr2 [, compare] )
The InStr function has these parts:
start Numeric expression 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 expression being searched.
strexpr2 String expression being sought.
compare Specifies the string-comparison 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. method.
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 Null If start or compare is Null, an error occurs., the function returns a Null.
Option Compare Statement
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.
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) & "."
Msg = "You didn't enter a letter of the alphabet!"
Msg = "You didn't enter anything."
MsgBox Msg ' Display message.