Query to find exactly matched words in Access

[petercheng]

Hi Folks,

My access records containing many words. I want to design a query that can search for the records and return whose contain exactly matched word(s). I tried the like statement, but it is not successful. Here is an exmaple: My record containing texts like < Purchasing Manager>; <retired Eventmanager>. If I used like "Manager" in query criteria, it does not return any record. If I used like "*Manager", it returned all records containing Manager, as well as other records that containing word like Eventmanager (which is not the record I look for). Are there any way I can have query that return records containing only exactly matched word(s)?

Peter

[alpinegroove]

What tables are in the query? Are they related? The join type might need to be adjusted.

[petercheng]

I am talking about just one table, and just to test the parameter query in it

[alpinegroove]

Can you remove sensitive information and upload the table?
Is that the only table in the database?
Troubleshooting steps to try to rule options out:
Copy the table to a new database and run the query there and see what happens.
Try to write the query in SQL view.

[petercheng]

Thanks Alpinegroove.
You can easily find this problem when you use the Northwind Trader database coming with Access 2007. For example, if you select Customer List table, then create query in table Customers. You just select one field Job titles in the query, where it contains records of several different job titles for the customers, such as Purchasing Representative, owner, Purchasing Manager, etc. Now, if you enter Like “Manager” in the criteria box, and run the query, nothing will come out. However, if you enter like “*manager”, all records that contain Manager will show up after running the query. However, this is not an exact matching of the parameter in query and data in the record. If you enter something like “*anager”, “*nager”, “*ger”, or even “*r” will return all the records that contain Manager. This is not what I want. I need an query that returns only exactly matched word. Any suggestion for how to solve this problem?
Peter

[orange]

If you really want an exact match, you must use =

However, if the job title is "Business Manager", you must say = "Business Manager".
If you use only "Manager" you will Not get that record.

You may want to think more about what you are trying to accomplish, and adjust as necessary. You may want to research the Like operator as well.

Also, if these Job Titles are being entered by users, you'll have to adjust for spelling mistakes. You may want to have a table of JobTitles and use a combobox or other means to select a value from an approved list. That, at least, would resolve spelling/capitalization issues.