Query not working to isolate specific words in a text string

[net]

Hello,

Can someone please help me isolate a particular word in a text string? I am using a query in Microsoft Access 2013.

I have a field called “Description”. I created 3 separate queries to use the same Description field. I need to see three separate columns using the same Description field, but pulling data with 3 different words. Sample below.

Description:
Work order part W0001 Indoor office building
Work order part W0001 Outdoor office building
Work order part W0001 in many offices

Query 1: Pulling data with the words “Indoor” and “W0001”

​

Work order part W0001 Indoor office building

​

Like "*INDOOR*" And Like "*W0001*"

Query 2: Pulling data with the words “Outdoor” and “W0001”

​

Work order part W0001 Outdoor office building

​

Like "*OUTDOOR*" And Like "*W0001*"

Query 3: Pulling data with the words “W0001”

​

Work order part W0001 in many offices

​

Like "W0001*"

Query 1 and 2 seem to work, but Query 3 is not working. It is still pulling all of the data with Indoor and Outdoor in the string.

 

[JHB]

...

Query 3: Pulling data with the words “W0001”
Work order part W0001 in many offices
Like "W0001*"
.. but Query 3 is not working. It is still pulling all of the data with Indoor and Outdoor in the string.

Oh yes it is working exact well, it is picking out all the records with the word "W0001" in it, and that is what you have told it to do!
To exclude the records with the words "Outdoor" and "Indoor" use "Not Like"

.. WHERE Description Like "*W0001*" And Description Not Like "*Outdoor*" And Description Not Like "*Indoor*";

Another way to do it is using an unmatch query.

 

[net]

Oh, LOL! Yes, that is what I am trying to do, is to exclude the words "Outdoor" and "Indoor".

How would I write that up in the query grid? I used this, but it did not work.

(Like "*W0001*") And Not Like “Outdoor*” And Not Like “Indoor*”

 

[JHB]

As the below:

Like "*W0001*" And Not Like "*Outdoor*" And Not Like "*Indoor*"

 

[net]

You my friend is a life saver! That totally worked!

Thank you sooooo much for taking the time to help me with my issue.

 

[JHB]

You're welcome, good luck.

 

[gemma-the-husky]

in passing, a routine that needs to analyse text strings in this way is not optimal.

If you have to do this, then could you add another column to store the result, maybe, so you do not have to examine the same records repeatedly.