Access VBA: Find a Character in a String

In Access VBA, use InStr to find a character or substring: it returns the character’s 1-based position, or 0 when no match is found. For example, InStr(1, "Access", "c", vbBinaryCompare) returns 3; use vbTextCompare instead when the search should ignore letter case.

What InStr Returns

InStr searches one string for another. The thing being searched for can be one character, such as "@", or a longer sequence, such as "Access". Its result is a position, not a True-or-False value. Positions begin at 1, so the first character is position 1; a result of 0 means the search text was not found.

The basic syntax is InStr([start], string1, string2, [compare]). The optional start argument specifies where the search begins. string1 is the text to search, and string2 is the character or text to find. The optional compare argument controls how VBA treats letter case and other text comparisons.

For instance, with sourceText set to "Access", searching for "c" returns 3 because the first c is the third character. Searching for "z" returns 0. Since the return value is a number, it is usually assigned to a Long variable.

A Basic Access VBA Example

This procedure demonstrates a case-insensitive search. It reports the position of the first V in the sample text, regardless of whether the letter is uppercase or lowercase.

Sub FindLetterExample()
    Dim sourceText As String
    Dim foundAt As Long

    sourceText = "Access VBA can find a character."
    foundAt = InStr(1, sourceText, "v", vbTextCompare)

    If foundAt > 0 Then
        Debug.Print "Found at position " & foundAt
    Else
        Debug.Print "Not found"
    End If
End Sub

The search begins at position 1. In this sentence, the first v is the V in “VBA,” at position 8, so the Immediate window displays Found at position 8. In the VBA editor, open the Immediate window with Ctrl+G on Windows. On Mac, the shortcut can differ by keyboard and editor version; use the View menu to open the Immediate window if the shortcut is unavailable. Before running a macro that changes data, save a copy of the database: macro changes cannot be undone with Ctrl+Z.

The If test matters. A common mistake is to check whether foundAt = 1, which only detects a match at the very beginning. To test whether a match exists anywhere, use foundAt > 0.

Choose the Right Comparison

The comparison setting determines whether uppercase and lowercase letters count as the same. For a predictable search, supply it explicitly rather than relying on a module’s comparison setting.

vbBinaryCompare performs a case-sensitive comparison. Searching for lowercase "a" does not match uppercase "A".
vbTextCompare performs a text comparison that ignores letter case for ordinary searches. It is useful for finding words or letters without regard to capitalization.
vbDatabaseCompare uses the sort order of the Access database. It can be useful when the desired comparison should follow that database’s collation behavior.
vbUseCompareOption tells the function to use the module’s comparison setting. This is less explicit, so it can make code harder to understand when the module setting is not nearby.

VBA modules can include an Option Compare statement near the top. If you omit the comparison argument, the module’s comparison option applies; if there is no such statement, VBA uses binary comparison. Access projects commonly use database comparison in modules configured for it, so do not assume every module has the same behavior. Passing vbBinaryCompare or vbTextCompare in the call makes the intention clear.

Start Searching After a Position

The first argument lets you continue searching after a previous match. This is useful when a string can contain the same character more than once. The following example prints every position of a period in the sample text:

Sub FindEveryPeriod()
    Dim sampleText As String
    Dim nextStart As Long
    Dim foundAt As Long

    sampleText = "One. Two. Three."
    nextStart = 1

    Do
        foundAt = InStr(nextStart, sampleText, ".", vbBinaryCompare)
        If foundAt = 0 Then Exit Do

        Debug.Print foundAt
        nextStart = foundAt + 1
    Loop
End Sub

The procedure prints 4, 9, and 16. After each match, it starts at the next position, rather than searching from the beginning again. If you set nextStart to foundAt instead, the same period would be found repeatedly and the loop would not make progress. Advancing by one is suitable when searching for individual characters or non-overlapping occurrences. For overlapping multi-character patterns, choose the next start carefully: advancing by one can allow overlaps, while advancing by the length of the search text skips them.

Search Backward with InStrRev

Use InStrRev when you need the last occurrence rather than the first. It searches from the end of the string by default and returns the position of the rightmost match. For example, InStrRev("report.final.docx", ".", -1, vbBinaryCompare) returns 12, the position of the final period. That can help identify a file extension or the last separator in a path-like string.

InStrRev has a different argument order from InStr: the string to search comes first, then the search text, followed by an optional starting position and comparison setting. This difference is easy to overlook when switching between the functions. Use InStr for a left-to-right search and InStrRev when the rightmost match is what matters.

Handle Null and Empty Strings

Access fields can contain Null, which is not the same as an empty string. When either string expression passed to InStr is Null, the function returns Null. If your code expects a numeric result, handle Null before assigning or comparing the result. A Variant lets you check the value safely:

Sub FindInOptionalText()
    Dim fieldValue As Variant
    Dim foundAt As Variant

    fieldValue = Null

    If IsNull(fieldValue) Then
        Debug.Print "There is no text to search."
    Else
        foundAt = InStr(1, CStr(fieldValue), "@", vbTextCompare)
        If foundAt > 0 Then
            Debug.Print "Found at position " & foundAt
        Else
            Debug.Print "The character was not found."
        End If
    End If
End Sub

In an actual form or recordset, replace the example assignment with the field or control value you need to inspect. Checking IsNull first avoids treating a missing value as ordinary text. For a query expression, Nz can substitute an empty string for Null; whether that is appropriate depends on whether a missing field should count as “not found” in your results.

Empty strings have defined behavior too: if the search text is empty, InStr returns the starting position; if the string being searched is empty and the search text is not, there is no match. If zero-length strings or Nulls are meaningful in your application, test them explicitly so that the result reflects your intended rule.

Use InStr in an Access Query

You can also use Access’s InStr function in a query expression to filter records. For example, a Criteria row can use an expression like InStr(1,[Description],"urgent",1)>0 to select descriptions containing “urgent,” without regard to case. The final argument, 1, represents text comparison. In a query, field references use square brackets, and a positive return value means a match exists.

Consider how Null descriptions should behave before using the expression. If you want Null to act like empty text, an expression such as InStr(1,Nz([Description],""),"urgent",1)>0 explicitly substitutes an empty string. This filters out Null rows because an empty string does not contain “urgent.” If you instead need to include Null rows for a separate reason, express that rule separately in the query criteria.

Searching with InStr is a substring test, not a whole-word test. A search for "art" also matches “partial.” If the requirement is to match a complete word, define the boundaries you consider valid and test them; simply finding the substring does not establish that it is a separate word.

Common Mistakes and Alternatives

Confusing position with a Boolean: InStr returns a number. Use > 0 to test for any match and = 0 to test for no match.
Using zero-based indexing: VBA string positions start at 1. Do not subtract one unless another part of your code specifically needs a zero-based index.
Assuming searches ignore case: specify vbTextCompare when that is required. A case-sensitive result may otherwise surprise you.
Forgetting Nulls: a Null result is not the same as 0. Check for Null before using values from optional Access fields.
Choosing the wrong function: InStr gives the first position; InStrRev gives the last. Replace changes matching text rather than reporting its position.

For a simple “does this text contain that text?” test, InStr(...) > 0 is usually the clearest choice. The Like operator is an alternative when you need a pattern with wildcards, such as a value that begins with a particular sequence. Its wildcard rules and purpose differ from an exact substring search, so use it only when a pattern is actually needed. For a single character’s numeric character code rather than its position, functions such as Asc address a different question; they do not locate that character in a longer string.

Frequently Asked Questions

How do I check whether a character exists in a string?
Call InStr and test whether its result is greater than zero. A positive result is the position of the first match; zero means it was not found.

Is InStr case-sensitive in Access VBA?
It depends on the comparison setting. Pass vbBinaryCompare for a case-sensitive comparison or vbTextCompare for a case-insensitive text comparison.

Does InStr return a zero-based position?
No. VBA string positions are 1-based. The first character is at position 1, and 0 is reserved to indicate that no match was found.

How do I find the last occurrence of a character?
Use InStrRev. It searches backward by default and returns the position of the rightmost occurrence.

Why does InStr return Null instead of zero?
One of the expressions being searched may be Null. Check it with IsNull before calling the function, or deliberately convert Null to empty text if that matches your application’s logic.

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.