Access VBA Find Text: Methods, Examples, and Fixes

To find text in Access VBA, use a DAO recordset’s FindFirst method with a criterion such as [LastName] = 'Garcia'. On a bound form, search its RecordsetClone and assign the clone’s bookmark to the form to display the matching record.

What “find text” can mean in Access

“Find text” can describe several different tasks in an Access database. You might want to move a form to the record whose name matches a search box, locate a row in a table or query, find every record containing a phrase, or search text across several fields. The best VBA method depends on which of these outcomes you need.

For finding a record in a DAO recordset, FindFirst is usually the straightforward choice. It evaluates a criteria expression and positions the recordset at the first match. Related methods—FindNext, FindPrevious, and FindLast—let you move among matches or start from the other end. These methods locate records; they do not search arbitrary text in every open object or automatically change a form’s displayed record.

Access’s built-in Find command is a separate feature. A user can search interactively from the interface, while VBA can call commands such as DoCmd.FindRecord in some interface scenarios. For code that needs a predictable criterion and a record to act on, DAO recordsets are generally easier to control.

Choose the right search method

Decide first whether you need a result on a form, a result in code, or a list of results. A form search should normally use the form’s own recordset clone so that the search follows the records available to that form. A table or query search can open a DAO recordset directly. If the goal is to return a value from one matching row rather than navigate to a row, a lookup or a parameterized query may be more suitable.

Navigate to one matching form record: use Me.RecordsetClone, FindFirst, and the form’s Bookmark.
Search a table or query in VBA: open a DAO recordset and use FindFirst.
Find successive matches: use FindNext and check NoMatch after each search.
Return one field value: consider DLookup for a simple lookup, or a query when you need more control over criteria and results.
Search many rows repeatedly: a query with criteria may be more efficient and easier to maintain than repeatedly scanning a large recordset.

Find text on a bound form

This example assumes a bound form whose record source includes a text field named LastName, a search text box named txtSearch, and a command button whose click event runs the procedure. It performs an exact match, so entering Garcia finds a record whose LastName is Garcia rather than every name containing those letters.

Private Sub cmdFind_Click()
    Dim rs As DAO.Recordset
    Dim searchText As String
    Dim criteria As String

    ' Read the search box safely; do nothing for blank input.
    searchText = Nz(Me.txtSearch.Value, vbNullString)
    If Len(searchText) = 0 Then
        MsgBox "Enter a last name to search for."
        Exit Sub
    End If

    ' Search the records currently available to this form.
    Set rs = Me.RecordsetClone
    criteria = "[LastName] = '" & Replace(searchText, "'", "''") & "'"
    rs.FindFirst criteria

    If rs.NoMatch Then
        MsgBox "No matching record was found."
    Else
        ' Move the form to the matching row.
        Me.Bookmark = rs.Bookmark
    End If

    rs.Close
    Set rs = Nothing
End Sub

The clone is a separate recordset cursor over the form’s records. Searching it does not itself move the form; assigning its bookmark to the form does. That distinction is why a recordset search can appear to succeed without changing what the user sees if the bookmark assignment is omitted. The search field must be present in the form’s record source, even if its control is hidden.

The example doubles apostrophes in the user’s input. Without that step, a name such as O’Neil can break the quoted criterion. This escaping is for a text value in an Access criterion; it does not make arbitrary field names safe. Use fixed, known field names in code rather than constructing a field name from untrusted input.

Search a table or query recordset

You can search a DAO recordset without a form. Open a dynaset from a table or query, call FindFirst, and inspect NoMatch before reading the current row. The following example searches the Customers table for an exact company name.

Public Sub FindCustomerByName()
    Dim rs As DAO.Recordset
    Dim searchText As String
    Dim criteria As String

    ' Replace this sample value with a value from your application.
    searchText = "Northwind Traders"
    criteria = "[CompanyName] = '" & Replace(searchText, "'", "''") & "'"

    Set rs = CurrentDb.OpenRecordset("Customers", dbOpenDynaset)
    rs.FindFirst criteria

    If rs.NoMatch Then
        MsgBox "Customer not found."
    Else
        MsgBox "Found customer: " & rs!CompanyName
    End If

    rs.Close
    Set rs = Nothing
End Sub

FindFirst takes a criteria expression, not a complete SQL statement: do not put WHERE at the start. The example uses a dynaset, a recordset type appropriate for this kind of search. A table-type recordset has different navigation features, including Seek, which requires an appropriate index. For many Access applications, a dynaset or a form’s clone is the more flexible option.

Exact matches, partial matches, and wildcards

An equality criterion searches for an exact field value. To find a phrase anywhere inside a field, use the SQL Like operator and wildcards. In a database using Access’s common ANSI-89 wildcard setting, an asterisk matches any sequence of characters. For example, the criterion for a company name containing the entered text can be built like this:

criteria = "[CompanyName] Like '*" & Replace(searchText, "'", "''") & "*'"

With searchText set to Traders, that criterion matches a value such as Northwind Traders Ltd. Access databases can use ANSI-92 SQL syntax instead; in that setting, the corresponding multi-character wildcard is percent (%) rather than asterisk (*). Check the database’s SQL syntax setting before copying a wildcard criterion between databases. The single-character wildcard also differs: it is ? in ANSI-89 and _ in ANSI-92.

Input that contains wildcard characters can broaden a partial search because Access interprets those characters as pattern operators. If the intended search is for a literal asterisk, question mark, percent sign, or underscore, handle that case deliberately according to the database’s wildcard rules. An equality search does not use Like pattern matching and is preferable when the requirement is an exact text value.

To search for an empty or null field, use criteria that reflects the data: an empty string is not always the same as Null in Access. For a null value, use a criterion such as [LastName] Is Null. For an empty string, use [LastName] = ''. Whether a field can contain either value depends on its design and how records were entered.

Find every matching record

FindFirst returns the first match in the recordset’s current order; it does not return all matches at once. To process every matching row, call FindFirst once, then call FindNext until NoMatch becomes true. The order of matches follows the recordset’s order, so define an order in the underlying query if a particular order matters.

rs.FindFirst criteria
Do While Not rs.NoMatch
    ' Read or process the current matching record here.
    Debug.Print rs!CompanyName
    rs.FindNext criteria
Loop

This loop belongs after opening and setting up the recordset in a procedure with a declared DAO recordset variable. If the procedure changes data, take care not to modify the same recordset in a way that disrupts the search. For read-only reporting, a query that selects only matching rows is often clearer: it avoids stepping through nonmatching rows and makes the result set explicit.

Common problems and how to fix them

The code says there is no match: confirm the field name is spelled correctly, is included in the recordset or form record source, and contains the expected value. Check leading or trailing spaces and whether the actual field is Null.
The criteria produces an error: text values need surrounding single quotes, and embedded apostrophes need doubling. Numeric and date criteria use different formatting rules; do not wrap every field value in text quotes.
A partial search finds unexpected rows: check whether the database uses ANSI-89 or ANSI-92 wildcards and whether the search input itself contains wildcard characters.
The form does not move: verify that the code searches Me.RecordsetClone and then assigns Me.Bookmark = rs.Bookmark when a match exists.
DAO is not recognized: Access normally provides DAO support, but a broken or missing library reference can cause compile errors. In the VBA editor, open Tools > References and check for a reference marked MISSING. Avoid adding a second, conflicting data-access library without a reason.
Nothing happens with a blank search: decide whether blank input should be rejected or treated as a search for empty values. Explicitly handle it rather than building a criterion accidentally.

Access text comparisons normally follow the database’s sort order and are commonly case-insensitive. If case-sensitive matching is a requirement, do not assume that FindFirst with equality enforces it. Retrieve candidate values and compare them with VBA’s StrComp using vbBinaryCompare, or use a query designed for the required comparison behavior.

Alternatives, performance, and safe use

For a single value from a known matching record, DLookup can be concise, but it returns a value rather than positioning a form or providing a cursor for additional fields. For complex conditions, multiple results, sorting, or repeated searches on a large table, a saved or parameterized query is often a better design. Queries make criteria visible and can let the database engine narrow results before VBA processes them.

Searching a form’s clone has a practical benefit: it searches the records already constrained by that form’s record source and filters. Conversely, it cannot find a row that the form’s query or filter excludes. If a record is missing from the search results, inspect the form’s filter, query criteria, and record source before changing the VBA search code.

Both examples above only locate and display records; they do not edit or delete data. Before running any VBA that does change records, make a backup copy of the Access database. Changes made by VBA are not reversed with Ctrl+Z, so test data-changing code on a copy first.

Frequently Asked Questions

How do I find a record in an Access form using VBA?
Use the form’s RecordsetClone, call FindFirst with a criterion, check NoMatch, and assign the clone’s bookmark to the form when a match exists.

How do I search for text anywhere in a field?
Use a Like criterion with wildcards around the search term. Use the wildcard characters that match the database’s SQL syntax setting: commonly * in ANSI-89 or % in ANSI-92.

What is the difference between FindFirst and FindNext?
FindFirst searches from the beginning of the recordset for the first match. FindNext continues from the current position to locate a later match using the same criterion.

Why does FindFirst return NoMatch when the text looks correct?
Check the actual field value, spaces, Nulls, the record source or filter, and apostrophes in the search text. For partial searches, also verify the database’s wildcard syntax.

Can FindFirst search more than one field?
Yes. Build a criterion with a logical expression, such as one condition joined to another with Or. Use the correct field names and data types, and escape text values before inserting them into the criterion.

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.