Not Like "*/CTO/*" not working

[NLR]

Hi,
I need a little help with the following access query.
I have a large table with many fields, three in particular that may contain the verbiage, /CTO/. I want to see all records except those that contain this verbiage.
My query is using, Not Like "*/CTO/*" in a separate Criteria row for each of these fields.
The results still returns records with the /CTO/. Not sure why?
Any help is appreciated.

Thanks,
NLR

 

[plog]

Can you post your SQL?

 

[NLR]

I'm not using SQL. I've attached a word doc of a picture of the query.
Please let me know if this will work for you.

Thanks,
NLR

 

[plog]

Yes, but just for reference, you can get the SQL from a query in Access by going to the 'View' icon in the top right of the screen on the Home tab of the ribbon. Drop the arrow down and an option is 'SQL View'.

Two more things before your issue: 1. Avoid special characters and spaces in field/table names (e.g. Routing Code, Reference #1). They make writing queries and code harder. 2. When you start numerating fields (e.g. Reference #1, Reference #2, etc.) its time for a new table. Instead of columns for additional Reference data, you should have rows for that data:

tblReferences
RoutingCode, ReferenceNumber, Reference
TP1257, 1, "Blah Blah Blah"
TP1257, 2, "More Stuff"
TP1257, 3, "Even More"
RC987, 1, "Reference Data"

Now your issue is a misunderstanding of OR and AND. Right now your code is using a bunch of OR statements:

If Field1 isn't /CTO/ OR Field2 isn't /CTO/ OR Field3 isn't /CTO/ include the record.

If any of those ORs evauluate to true then the whole thing is true and the record shows. So if Field1 contains /CTO/ and Field2 contains /CTO/ but field3 doesn't, the record shows because there's one true in there (Field3 isn't /CTO/).

You want to use AND. That means each evaluation has to be true for the whole thing to be true. To do that, instead of using 3 seperate lines, put your criteria on the same line.