Others

Fix Microsoft Access Like Query Not Finding Records in Rich Text

Khadija KhanKhadija Khan Nov 2, 2026 869 views

Question details

Users are unable to retrieve all matching records when using the Like operator in a Microsoft Access query on a rich-text formatted field.

How to Fix Microsoft Access Like Query Not Finding Records in Rich Text Fields
Product
Microsoft Access
Device & OS
not provided
Scenario
Filtering data using the Like criteria (such as Like "T*") on a Long Text field that contains rich-text formatting.
Observed behavior
The query fails to return expected records, even when the visible text clearly matches the criteria, because hidden HTML formatting markup alters the underlying stored string.
Before you start

Before modifying your query criteria or changing field data types, ensure you create a backup copy of your Microsoft Access database to prevent any accidental data loss.

Solution 1Recommended

Use the PlainText Function to Ignore HTML Markup

Applying the PlainText function in your query strips away hidden rich-text HTML formatting before the Like criteria is evaluated, allowing the query to match the visible text accurately.

Rich text fields in Access store HTML tags (like <div> or <strong>) alongside your data. When you search using a wildcard like "T*", the query reads these hidden HTML tags at the beginning of the string instead of your actual text, causing the match to fail.

1
Open the query in Design View

Right-click your query in the Navigation Pane and select Design View to modify your query columns.

2
Apply the PlainText function

In the Field row of the query design grid, modify your field reference to use the PlainText function. For example, type: PlainText([YourFieldName]).

3
Enter your Like criteria

In the Criteria row beneath the newly modified field, enter your desired wildcard search, such as Like "T*".

4
Run and test the query

Click the Run button (!) on the ribbon to execute the query and confirm that the missing records are now successfully returned.

Use the PlainText Function to Ignore HTML Markup
Accurate Matching Achieved: By stripping the HTML markup during the query execution, Access correctly matches wildcard criteria against the actual visible characters.
Free Microsoft Office alternative

Need a Free, Lightweight Office Suite? Try WPS Office

While Microsoft Access manages specialized database queries, you can rely on WPS Office for all your everyday document, spreadsheet, and presentation needs. It's a comprehensive, lightweight, and cost-free alternative that guarantees seamless compatibility with your existing Office files.

  1. 1. Visit the official website: Go to the official WPS Office website to access the latest free download.
  2. 2. Download the installer: Click the Free Download button that corresponds to your operating system.
  3. 3. Install and launch: Run the downloaded installer file and follow the simple on-screen instructions to get started.
Fully compatible with Microsoft Office formats, including Excel (.xlsx) for seamless data organization and analysis.Provides a familiar, highly intuitive tabbed interface, making the transition from Microsoft Office completely effortless.A lightweight installation that ensures rapid performance even on older devices, saving system resources.Completely free to download and use across Windows, Mac, Linux, iOS, and Android platforms.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access query return fewer records than expected in a rich text field?

Rich text fields in Access store background HTML tags (like <div> or <span>) alongside your actual text. When you query data using wildcards and the Like operator, Access evaluates the entire stored string, including these hidden tags, which causes the match to fail against your visible criteria.

What is the difference between Rich Text and Plain Text formats in Microsoft Access?

Rich Text allows you to format specific words with bolding, italics, and colors by embedding HTML code into the data string. Plain Text, however, stores only the raw alphanumeric characters you type, which prevents formatting but ensures maximum compatibility and accuracy when performing database queries.

How can I check if there are hidden characters in my Access table field?

You can use the Len() function in an Access query (e.g., Len([YourFieldName])) to calculate the total number of stored characters in the record. If the returned number is significantly larger than the length of the visible text you see on the screen, the field contains hidden spaces or rich-text HTML formatting.