Others

How to Find a Substring in an Access Text Field

Muhammad Talha Oct 1, 2026 868 views

Question details

The user needs to locate specific character strings or substrings anywhere within a text field in a Microsoft Access database.

How to Find a Substring in an Access Text Field

Product: Microsoft Access
Device & OS: not provided
Scenario: Writing an Access query to filter and find records that contain a specific text pattern.
Observed behavior: Using wildcard pattern matching and the LIKE operator to successfully extract matching records.

Before you start

Ensure you have basic familiarity with writing SQL queries in Microsoft Access and know the specific table and field names you want to search.

Solution 1Recommended

Use the LIKE Operator with Wildcard Characters

The most straightforward way to find a substring anywhere in a text field is by using the LIKE operator combined with asterisk (*) wildcards.

In Microsoft Access, the asterisk (*) is used as a wildcard character to represent zero or more characters. By placing an asterisk before and after your search term, you instruct the database to find that specific sequence of characters anywhere within the text field.

Open Query Design

Open your Access database, navigate to the Create tab on the ribbon, and click on Query Design.

Switch to SQL View

Close the Show Table dialog if it appears, right-click the query tab, and select SQL View to manually enter your SQL statement.

Write the LIKE Query

Type your query using the LIKE syntax. For example, to find 'abc' anywhere in the CustomerName field, type: SELECT * FROM Customers WHERE CustomerName LIKE "*abc*";

Run the Query

Click the Run button (the red exclamation mark) in the Query Design ribbon to execute the query and view your filtered records.

Use the LIKE Operator with Wildcard Characters

Quick Tip: You can also use this approach directly in the Criteria row of the Access Query Design grid by typing Like "*abc*" under the desired field.

Solution 2

Create a VBA Function to Match Complete Words

If you need to find exact whole words rather than partial substrings, standard wildcards may return false positives, making a custom VBA function necessary.

Free Microsoft Office alternative

Easily Search and Filter Data with WPS Office Spreadsheets

While Microsoft Access is a dedicated database tool, many data filtering, searching, and substring matching tasks can be effortlessly handled using WPS Office Spreadsheets. WPS Office is a free, lightweight alternative that is fully compatible with Microsoft Office formats, offering seamless data management.

1. Open Your Data File: Export your Access table as a CSV or Excel file and open it in WPS Spreadsheets.
2. Use the Find Feature: Press Ctrl + F to open the Find and Replace dialog.
3. Search for Substrings: Type your desired substring and click 'Find All' to instantly highlight and locate all matching records in your dataset.

Free and lightweight Office suite for everyday tasksFully compatible with Microsoft Excel (.xlsx) and CSV formatsAdvanced text filtering and search functions for large datasetsFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the percentage sign (%) instead of an asterisk (*) as a wildcard in Access?

In standard Microsoft Access databases (ACCDB/MDB), the asterisk (*) is the default wildcard for the LIKE operator. The percentage sign (%) is generally used only if the database is configured to use ANSI-92 SQL syntax or if you are querying a SQL Server backend.

How do I find a substring that actually contains a literal asterisk?

To search for a literal asterisk in your text field, you must enclose it in square brackets within your LIKE statement. For example, to find strings containing '*abc', you should use the criterion LIKE "*[*]abc*".

Is the LIKE operator case-sensitive in Microsoft Access?

No, by default, the LIKE operator in Microsoft Access is not case-sensitive. Searching for "*abc*" will successfully match "ABC", "abc", and "aBc".

Office Quick Solutions