What am I not getting in this logical array?

[Slurry Pumper]

Hey all

I am setting up a description filter, and in the queries Criteria section I place a line as shown:

Like "*BRKT*" And Not Like "*MOTOR*"

What I am trying to do is list all of the items with "BRKT" in the title, but not the ones that have "BRKT" and "MOTOR" in the description. As an example, I am trying to filter out things like:

BRKT MOTOR #1.214.08.0.569.00 ROVEMA



I thought the Like / Not Like command I had would do the trick, but it ain't happening, so I guess I am thinking of the logical determinants incorrectly. Can anyone straighten out my approach?

[aytee111]

Like "*BRKT*" And Not Like "*BRKT*MOTOR*"

[Slurry Pumper]

Thanks aytee111

I was thinking that I really do want "MOTOR BRKT" but not "BRKT MOTOR", and the example you give will do that unless of course the nomenclature is "MOTOR BRKT MOTOR", in which case I believe an error in data entry has occurred, and would need to be corrected.

This doesn't explain why the original Like / Not Like line doesn't work. The way the operator is explained in the help section contained with the software makes it seem as though it would work, and I can't think of why it wouldn't filter out all things with "MOTOR" in it. If both conditions are present, I can't see why the program includes the line in the table I have it saving to when I specifically instruct the program to not include all such occurrences.

[aytee111]

Things get very complicated once we start mixing and matching - and's and not's and or's - our brain has a hard time processing it. Computer systems have a fixed way of doing and not all are the same as well. Your "doesn't work" isn't very explanatory, but you have 2 statements - any like BRKT, none like MOTOR. How does Access process those two, in what sequence, together or separately?

Having a "NOT" in a sentence gets the brain quite muddled, use them sparingly.

[Slurry Pumper]

Well given my list of particulars that I am trying to parse through, it seems to me that I will be a failure at filtering out the data base as I had hoped utilizing the Like / Not Like, in conjunction with the And / Or arguments. Below is how I changed the criteria around, and to the side the results are listed. I still get some undesired data that is proving difficult to get rid of. Despite the use of a particular arrangement of utilized commands with special characters or anything else I have tried so far. I read another thread on this subject that is right in the same area of the forum listing, and on that thread, there seems to be a new command of WHERE that is introduced. I'm not holding out much hope however, Like and Not Like seem pretty straight forward enough, but they are proving to be not as advertised in my opinion, and apparently require a perverse convolution of tribal knowledge to implement even a seemingly simple task. I have been reading other topics on a command named "In", that seems like it would limit my need to list the same text repeatedly in my criteria area, but that seems like some kind of graduate thesis kind of stuff to deal with if the other commands are any indication. I think at this point, I will tell my people to ignore the BS lines that don't relate to the intended search so that I can return to my actual job.

Good luck to all of the data manipulators on this site seeking advice, I'm sure that there are suitable techniques that once employed, will yield spectacular results. As for me, I going to go ponder the meaning of all the words involved in the commands discussed on this thread and see if it really is me or just the programming of the software that is the root cause of my non-understanding. I think there may be a good reason why not many people understand this program well enough to get anything from it, even though I can see the powerful results some people seem to be able to obtain from it.

[aytee111]

With "IN" (or NOT IN) you need a list of items, that won't suit you. The criteria you are entering in your query is the WHERE - go to View/SQL and see that Access puts the WHERE in for you.

To make your selections 100% foolproof, make each one its own separate query so that your first query says Like "*BRKT*" and your second reads that query as input and has the Not Like "*BRKT*MOTOR*".

And yes, I agree with you, people go into Access thinking it is just another Office product, but it is a whole different kettle of fish!

[Slurry Pumper]

It seems to work better when I break it up in to separate queries, one to create the table, and the other to extract data that I undesirable.

Thank You.

[Micron]

I agree the AND, OR, IN, NOT operators can be confusing, but AND and OR are the hardest.
Like "*BRKT*" And Not Like "*MOTOR*" : when you think about it, you are asking to retrieve any one thing that satisfies BOTH of the conditions you have imposed - at the same time. I tried something on a limited set of test data and it seemed to return what you're looking for. I apologize if not since I was using my own data and applied conditions similar to yours. Translated, the Where clause would be

Like "*BRKT*" And Not Like ("*BRKT*" & "*" & "*MOTOR*")
If nothing else, it shows that you can concatenate portions and the query will accept it. That alone might help you.
The application of sql operator AND and so forth is pretty much universal, so don't blame Access.

[John_G]

In your query, you have all the different combinations on separate lines, meaning that they are all joined by OR's.

What that means is that if ANY ONE of the conditions is true, then the whole expression will be true.

for example, take the test expression: "BRKT MOTOR 1.2.3.4 ROVENA"

The criteria expression Like "*BRKT*" And Not Like "*MOTOR*" will be false, because Not Like "*MOTOR*" is false.However, the criteria expression Like "*BRKT*" And Not Like "*DRV*" is going to be True, because Like "*BRKT*" is true (it contains BRKT),
and Not Like "*DRV*" is also True (it does not contain DRV), so the whole criteria expression is True. Therefore, that record will meet the query criteria, regardless of what the other expressions evaluate to, because they are joined by OR's.

In order to do what you want, you would need to put ALL those criteria expressions together, joined by AND's, in one line (not a pleasant thought!)

What you could do is write a user-defined-function to do all the checking for you and return True or False, and use the function in the query.