Access VBA Find in String: InStr, Options, and Examples

In Access VBA, use InStr to find one string inside another: it returns the 1-based position of the first match, or 0 if there is no match. For example, InStr(1, textToSearch, searchFor, vbTextCompare) performs a case-insensitive search; use vbBinaryCompare for a case-sensitive comparison.

How InStr works

InStr takes a starting position, the text to search, the text to find, and an optional comparison setting. Its general form is InStr([start], string1, string2, [compare]). The start position is counted from the beginning of string1, and the returned position is also counted from the beginning of that string. Positions begin at 1, not 0.

For example, searching for cat in concatenate returns 4, because the first c in the matching sequence is the fourth character. If the search term is absent, the result is 0. That makes a simple greater-than-zero test a practical way to check whether text exists.

A result greater than 0 means a match was found; the number identifies its position.
A result of 0 means no match was found.
A result of Null is possible when an input is Null, so handle nullable data before treating the result as a number.

The first argument can also be used to search from a later position. If you want the second occurrence of a term, find the first occurrence, then call InStr again beginning one character after it. This is useful when a string may contain repeated separators or keywords.

A basic VBA example

This procedure searches a sample sentence without regard to letter case and reports either the position or that the term was not found. The explicit comparison argument makes its behavior clear regardless of the module’s comparison setting.

Public Sub FindTextExample()
    Dim textToSearch As String
    Dim searchFor As String
    Dim foundAt As Long

    ' Sample text and search term.
    textToSearch = "Access VBA can search a string."
    searchFor = "VBA"

    ' Search without distinguishing uppercase and lowercase letters.
    foundAt = InStr(1, textToSearch, searchFor, vbTextCompare)

    If foundAt > 0 Then
        MsgBox "Found at character " & foundAt
    Else
        MsgBox "Not found"
    End If
End Sub

Here, foundAt is 8, because the V in VBA is the eighth character in the sample sentence. Before running a macro, save a copy of the database or workbook you are working with; VBA changes are not undone with Ctrl+Z. This example only displays a message, but the same rule is worth following for procedures that update records.

Choose case-sensitive or case-insensitive matching

The optional compare argument controls how Access compares characters. Use vbTextCompare when uppercase and lowercase versions should count as the same text, such as finding urgent in URGENT: call back. Use vbBinaryCompare when letter case should matter. Binary comparison follows character values, while text comparison follows textual comparison rules that can be affected by locale.

If you omit compare, VBA uses the module’s Option Compare setting. A module can specify Option Compare Binary, Option Compare Text, or, in Access, Option Compare Database. If no setting is specified, binary comparison is the default. The setting belongs at the top of a module, before its procedures.

For code that must behave consistently when copied to another module or maintained later, pass vbTextCompare or vbBinaryCompare explicitly. Avoid relying on an unseen module setting when the distinction affects a result, for example when checking a username, code, or status label.

Handle Null values and empty strings

Access tables often allow fields to contain Null, which means there is no known value. It is different from an empty string, which is a value containing zero characters. If either string argument supplied to InStr is Null, the result can be Null. A test such as If InStr(...) > 0 Then is therefore unsafe when the search text may come directly from a nullable field.

Check for Null first, or convert it deliberately to an empty string. For instance, in VBA, If Not IsNull(value) Then can guard the search. In an Access query, Nz([Notes],"") can substitute an empty string for a Null field. Decide what Null should mean in your application: skipping the record and treating it as having no searchable text are not always equivalent.

An empty search term also deserves attention. Searching for an empty string does not answer the same question as searching for a real word; the documented behavior can return the start position. Validate user-entered search terms if a blank term should not match anything. For example, require that the term has at least one character before running the search.

Search a field in an Access query

InStr is also available in Access query expressions. To return records whose Notes field contains “urgent,” add a calculated criterion such as InStr(1,[Notes],"urgent",1)>0. In this expression, the final value 1 requests a text comparison. If Notes can be Null, use InStr(1,Nz([Notes],""),"urgent",1)>0 so that the expression operates on an empty string instead.

The same idea can be used in a calculated query column. For example, an expression that returns the position of a term can help inspect where a match occurs. A criterion ending in >0, by contrast, filters the query to matching records. Test expressions on a small sample and check how they behave for Nulls and blank fields before relying on them in a form or report.

Do not confuse this with the FindFirst method on an Access recordset. InStr searches within a string value; FindFirst searches a recordset for a record meeting a condition. Choose based on whether the thing you need to locate is a substring inside a field or a matching database row.

Search from the end with InStrRev

Use InStrRev when you need the last occurrence of a substring rather than the first. Its arguments are ordered differently: the string to search comes first, followed by the term to find, with optional start and comparison arguments. It returns the position counted from the left edge of the original string, even though the search proceeds from right to left.

For example, the final period in a filename can be located with InStrRev(fileName, "."). If the result is greater than 0, it marks the last period’s position. This can be useful for separating a filename from its extension, but it does not validate that the extension is allowed or that the input is a valid path. Account for names with no period, and consider whether a period in a folder name changes the result for a full path.

As with InStr, use an explicit comparison argument when case sensitivity matters, and check for Null before passing database values into the function. InStrRev is not simply a way to get a normal position from a forward search; it is the suitable choice when the rightmost match is specifically required.

Common problems and useful alternatives

A frequent mistake is treating the returned number as a true-or-false value. In VBA, use foundAt > 0 to test for a match. Another is assuming a search is case-insensitive by default; the default depends on the module’s comparison setting, so state the desired rule explicitly.

Need to test whether a value contains a term? Use InStr(...) > 0.
Need to check whether text starts with a term? Use InStr(1, value, term, compare) = 1, after handling Null and blank inputs.
Need the last match? Use InStrRev.
Need a wildcard pattern? Consider the Like operator, while accounting for the database’s ANSI-89 or ANSI-92 pattern settings.

Like is an alternative when the condition involves a pattern rather than a literal substring. Its wildcard characters and behavior differ from InStr, and Access databases can use different ANSI pattern modes. For a straightforward “does this exact piece of text occur anywhere?” check, InStr is usually easier to read and less ambiguous.

Frequently Asked Questions

What does InStr return in Access VBA?
It returns the 1-based position of the first occurrence of the search string, or 0 if no match is found. If an input is Null, the result may also be Null, so check nullable values before comparing the result.

How do I make InStr case-insensitive?
Pass vbTextCompare as the fourth argument, as in InStr(1, sourceText, searchFor, vbTextCompare). This avoids relying on the module’s Option Compare setting.

How do I find the last occurrence of text?
Use InStrRev. It searches from the right side of the string and returns the matching position using the normal left-based character numbering.

Can I use InStr in an Access query?
Yes. Use an expression such as InStr(1,[Notes],"urgent",1)>0 as a criterion. If the field can be Null, wrap it with Nz or otherwise handle Nulls deliberately.

Why does my InStr test fail when a field is blank?
A blank string and Null are different. A blank string has a value but no characters; Null means no value. Check with IsNull in VBA or use Nz in a query when replacing Null with an empty string is appropriate.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.