How to get matching words in string of characters.

[fcp]

Hi,

I have a field called Produce that has the following values/data.



PRODUCE

Box of apples
Box of Pineapples
Crates of Apples

My query criteria is as follows to pick up apples only!

Like "*" & [PRODUCE:] & "*"


When I type in "apples" the criteria also returns "Pineapples" in the record. Is there a way in Access to pick up only "apples" and ignore "pineapples" in the selected record?

Thanks

[orange]

Try " apples" a space before the a

[fcp]

I guess I can tell users to do that. I was hoping there is a way for Access to get the exact words within a group of string rather than grouping the characters. So for example in a string of "box of apples" there is a wildcard to pick only individual apples as a whole word rather than picking up "pineapples" as well because "pineapples" contains the string "apples."

If there is no way then its Ok.

Thanks

[orange]

Well look at it this way. You want to find an exact string - That isn't the issue. The reality is that there are 2 words within your field values that contain "apples". In order to find an exact match you have to search for the exact match using a unique string.
That is the search you want to achieve.

You want to find the exact target and only that target, so in your case you must use
" apples". As you have found for yourself, "apples" will find all occurrences of apples, whether it is "bag of apples","case of Pineapples" or "snapples" or crabapples....

I notice you are a relatively new poster, welcome and continue learning by doing.

Here is a link to Crystal's material which is an excellent collection of good practices and instruction. http://www.accessmvp.com/strive4peace/

[fcp]

I think I got the logic behind the solution you given. Thanks again!