Fix Microsoft Access Like Query Not Finding Records in Rich Text
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.

- 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 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.
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.
Right-click your query in the Navigation Pane and select Design View to modify your query columns.
In the Field row of the query design grid, modify your field reference to use the PlainText function. For example, type: PlainText([YourFieldName]).
In the Criteria row beneath the newly modified field, enter your desired wildcard search, such as Like "T*".
Click the Run button (!) on the ribbon to execute the query and confirm that the missing records are now successfully returned.

Check Stored Field Lengths and Trim Spaces
Use the Len and Trim functions to identify and eliminate formatting discrepancies caused by invisible characters or leading spaces.
Convert the Field Format to Plain Text
If rich-text styling is not strictly necessary for your data, converting the field to plain text permanently resolves all formatting-related query issues.
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. Visit the official website: Go to the official WPS Office website to access the latest free download.
- 2. Download the installer: Click the Free Download button that corresponds to your operating system.
- 3. Install and launch: Run the downloaded installer file and follow the simple on-screen instructions to get started.

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.




