Others

How to Search a Long Text Field from an Access Form

Natalie Taylor Oct 22, 2026 869 views

Question details

The user needs to search for specific words or phrases within a long text field directly from a Microsoft Access form.

How to Search a Long Text Field from an Access Form

Product: Microsoft Access
Device & OS: not provided
Scenario: Setting up a user-friendly search interface on an Access form using a text control and wildcards.
Observed behavior: Standard query searches work in Design view but fail when executed via a form control without proper VBA filtering.

Before you start

Back up your Microsoft Access database before modifying form designs or adding VBA code to prevent accidental data loss.

Solution 1Recommended

Filter Using an Unbound Text Box and VBA

Use an unbound text box to capture the search term and apply a VBA filter in the After Update event.

By using an unbound text box, you can accept user input without altering your underlying data. VBA code can then take this input, combine it with wildcard characters, and filter the form's recordset.

Add an Unbound Text Box

Open your Access form in Design View. Insert an 'Unbound Text Box' to act as your search field and name it appropriately (e.g., txtSearch) in the Property Sheet.

Access the After Update Event

Select the newly created text box, navigate to the Property Sheet, and click on the 'Event' tab. Find the 'After Update' property.

Insert the VBA Filter Code

Click the ellipsis (...) to open the VBA editor. Enter the following code to build and apply the filter: strFilter = "YourFieldName LIKE '*" & Me.txtSearch & "*'" followed by DoCmd.RunCommand acCmdApplyFilterSort.

Test the Search Functionality

Save your changes and switch to Form View. Type a keyword into the text box and press Enter to see the filtered results based on the long text field.

Filter Using an Unbound Text Box and VBA

Wildcard Matching: Using asterisks (*) as wildcards ensures the form searches for the specified string anywhere within the long text field.

Solution 2

Use a Related Notes Table for Better Data Structuring

Instead of storing multiple entries in a single long text field, split them into a one-to-many related table for easier and more reliable searching.

Free Microsoft Office alternative

Try WPS Office for Your Document and Spreadsheet Needs

While Microsoft Access is a specialized database tool, you can manage your exported database reports, spreadsheets, and documentation efficiently with WPS Office. It provides a lightweight, free, and highly compatible suite for your everyday document tasks.

1. Download the Installer: Visit the official WPS Office website to download the free installation file.
2. Install the Suite: Run the installer and follow the simple on-screen instructions to set up the software.
3. Open Exported Data: Launch WPS Spreadsheets to seamlessly view, edit, and analyze reports exported from your database.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Easily format and analyze exported database data in WPS Spreadsheets.Lightweight installation that runs smoothly on most operating systems.Free to use with a familiar, tabbed user interface for easy multitasking.Built-in PDF editing and conversion tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the wildcard search return false substring matches?

The LIKE operator with asterisks (*) matches any string containing those characters, even if they are part of a larger word (e.g., searching for 'cat' will match 'category'). For exact whole-word searches, you may need to utilize a custom VBA function or a dedicated search module.

Can I search multiple text fields at once using the unbound text box?

Yes, you can expand your VBA filter string using the OR operator. For example: Me.Filter = "Field1 LIKE '*" & Me.txtSearch & "*' OR Field2 LIKE '*" & Me.txtSearch & "*'".

What is an unbound text box in Microsoft Access?

An unbound text box is a control on a form that is not directly connected to a specific field in an underlying table or query. It is typically used to temporarily capture user input, such as search terms or calculation parameters.

Why is my form filter not applying automatically after I type?

Ensure that your VBA code executes a command to turn the filter on, such as setting Me.FilterOn = True or running DoCmd.RunCommand acCmdApplyFilterSort immediately after the filter string is defined.

Office Quick Solutions