Search query problem

[ZOR]

I though this query problem was dead and buried, however user complaining its ommiting records. It searches a field of text named comments. If the user types in a word eg "Swimming" then it should retrieve all records where the word swimming appears, whether at the front, middle, end of the text. I have tried sevral attempts the last being:

& "WHERE (TXCLIPS.Comments Like '" & Me![LNAME14].Caption & "*' OR TXCLIPS.Comments Like '*, " & Me![LNAME14].Caption & "*')" _

Any ideas, thanks

 

[lespaul]

Why not just:

WHERE TXCLIPS.Comments LIKE '*" & Me![LNAME14].Caption & "*'"

Like works like this:

LIKE *AC - finds all words that END in AC
Lilac
Prozac

Like AC* - finds all words that BEGIN in AC
Access
Account

LIKE *AC* - finds all words that CONTAIN AC
Access
Account
Lilac
Prozac
Back
Pack

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ZOR]

Thanks. However using this:

& "WHERE TXCLIPS.Comments LIKE '*" & Me![LNAME14].Caption & "*'" _

If I type the word "Go" it brings up records with having more than go, ie good, goal etc.

I tried Go, and used the space bar but it still brought up goal etc.

Thanks

 

[lespaul]

that's because both of those words contain the letter combination GO (did you not understand the explanation above?)

good
goal

it will also find words like:

ago
algorithym

now the chances of a word containing the letter combination "swimming" are probably not as common as words containing the go letter combination.