MS Access InstrRev() Function

The InstrRev() function in MS Access finds the starting position of a substring within a string, but it searches backward from the end of the string. This is helpful when you need to locate the last occurrence of a substring or when you want to work with the rightmost part of a string.

InstrRev(): Definition and Usage

Unlike the Instr() function, which searches forward from the beginning, InstrRev() searches backward from the end of the string. The search is case-insensitive by default. You can optionally specify a starting position and the type of string comparison to use.

Syntax

Syntax

InstrRev(string1, string2, start, compare)

Parameter Values

Parameter Description
string1 The string to search within. This is required.
string2 The substring to search for. This is required.
start (Optional) The starting position for the search (default is -1, searching from the end).
compare (Optional) The comparison method:
  • -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 Last Occurrence of a Substring

This example searches for "t" in "Customer", starting from the end of the string.

Syntax

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

5