How to Search an Access Long Text Field for a Complete Word
Question details
User needs to query a Microsoft Access Long Text field to find records that contain a complete word or phrase rather than any substring match, using case-insensitive Access comparisons.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The user is building or editing an Access query and wants to locate rows where a Long Text field contains a specific word or phrase as a complete unit.
- Observed behavior
- A basic LIKE search matches text anywhere in the field, so it can return partial-word results; complete-word matching requires a custom FindWord function or an equivalent boundary check.
Open the target database in Microsoft Access and know the table name and Long Text field name you need to search. If you plan to use complete-word matching, make sure the Access database can open the Visual Basic for Applications editor and create or import a module.
Use LIKE to Search a Long Text Field for Any Substring
Choose this method when you only need to find records that contain the text anywhere in the Long Text field, not necessarily as a whole word.
In Microsoft Access, click Create > Query Design, close the Show Table dialog, and switch to SQL View.
Type SELECT * FROM YourTableName WHERE YourFieldName LIKE "*january*"; and replace YourTableName and YourFieldName with your actual table and field names.
Click Run in the Query Tools Design tab or press Ctrl+Enter to execute. Access text comparisons are case-insensitive by default.
Match a Complete Word or Phrase with the FindWord Function
Use this method when the basic LIKE search returns unwanted partial matches and you need to match the complete word or phrase only.
Use WPS Office as a Free, Lightweight Alternative for Everyday Office Tasks
WPS Office cannot query Microsoft Access databases, but for teams that do not need a full relational database, WPS Office provides a free, lightweight way to create, edit, and share Word, Excel, and PowerPoint-compatible files with a familiar ribbon interface and seamless migration.
- 1. Download WPS Office: Click Download WPS Office Free and run the installer on your Windows, macOS, Linux, iOS, or Android device.
- 2. Open your existing documents: Launch WPS Office and open your Microsoft Word, Excel, or PowerPoint files directly from the file picker or by dragging them into WPS.
- 3. Use WPS Spreadsheets for tabular data: Open WPS Spreadsheets to filter, search, and organize data when an Access database is unnecessary.
Frequently Asked Questions
How do I search an Access Long Text field without case sensitivity?
Access text comparisons are case-insensitive by default. Use a query such as SELECT * FROM YourTableName WHERE YourFieldName LIKE "*january*"; to match the text anywhere in the field.
Why does LIKE return partial matches like "January" inside "JanuaryReport"?
The asterisk wildcard matches any surrounding characters, so LIKE "*january*" finds any occurrence. To match a complete word only, call a FindWord function from the query that checks word boundaries.
Where do I enter the SQL statement in Microsoft Access?
Click Create > Query Design, close the Show Table dialog, switch to SQL View, paste the SQL, and then click Run.
Can I match a complete phrase instead of a single word?
Yes. Use the same complete-word approach with the full phrase, or use a regular-expression word boundary pattern that includes spaces or punctuation before and after the phrase.
Does WPS Office include Microsoft Access?
No, WPS Office does not include an Access database equivalent, but it works with Word, Excel, and PowerPoint formats and can handle many everyday Office tasks for free.
