Not like or Not In?

P

pgarcia

I'm having a problem with the following:
Not Like ("BAX")
Not In ("BAX")

The above does not seem to work with the follwoing:
U S A'BAX'STATION'PARENT ACCT

They do not seem to find the work BAX in customer name. I'm trying to remove
anything contaning "BAX" in my list.

Thanks
 
D

Dirk Goldgar

pgarcia said:
I'm having a problem with the following:
Not Like ("BAX")
Not In ("BAX")

The above does not seem to work with the follwoing:
U S A'BAX'STATION'PARENT ACCT

They do not seem to find the work BAX in customer name. I'm trying to
remove
anything contaning "BAX" in my list.
Click to expand...


You need to use one or more wild-card characters in the Like pattern. Try
this:

Not Like "*BAX*"

That should work for a query being run normally in an Access .mdb or .accdb
file. Under some circumstances, you may need to use the ANSI wild-card
character '%' instead of '*', but those are uncommon, so I won't get into
that unless the above doesn't work.
 
J

Jeff Boyce

If you want to use "Like", you'll need to use the wildcard characters.

Try

Not Like *BAX*

This tells Access to look for "BAX" ANYWHERE within a field (zero-to-many
characters before, zero-to-many characters after).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

pgarcia

Ah jes. I knew that. I was using "%" as the list of data I was getting it
from used SQL. Thanks
 
K

KJMachado

I am also having a similar problem in my query. I want to exclude an
Organization in my report.

I've tried:

NOT LIKE "W.EP.03"
NOT LIKE "*EP*"
NOT LIKE "W.EP???"
NOT LIKE "%EP%"

I finally went to the Organizations Table, added the OrgAbb Field to my
query (instead of using the Cust_Org Field in the Custodians Table), and
typed Not Like "W.EP.03" which gave me the results I needed.

I noticed I got a message once concerning the dot. What should I be aware
of in using my Organizations Abbreviations in filters and/or queries?

Thank you for your response.

Kathe
 
J

John W. Vinson

I am also having a similar problem in my query. I want to exclude an
Organization in my report.

I've tried:

NOT LIKE "W.EP.03"
NOT LIKE "*EP*"
NOT LIKE "W.EP???"
NOT LIKE "%EP%"

I finally went to the Organizations Table, added the OrgAbb Field to my
query (instead of using the Cust_Org Field in the Custodians Table), and
typed Not Like "W.EP.03" which gave me the results I needed.

I noticed I got a message once concerning the dot. What should I be aware
of in using my Organizations Abbreviations in filters and/or queries?

Thank you for your response.

Kathe
Click to expand...

Just to clarify:

The function - the *ONLY* function - of the LIKE operator is to allow
wildcards (* meaning match any string of characters, ? match any single
character, # match any digit, etc.) If your criterion does not use any
wildcards, then you don't need to use the LIKE operator - it will simply be
treated the same as the = operator, but will probably have poorer performance
since the query engine must check for wildcards.

If you want to exclude Organization W.EP.03 then simply use the not-equals
operator as a criterion:

<> "W.EP.03"

If you want to exclude a list of organizations, then you can use the NOT IN()
clause:

NOT IN("W.EP.03", "K.XY.56")

If you want to exclude all of the W.EP organizations, whatever the last two
digits might be, *then* you need LIKE:

NOT LIKE "W.EP.*"

will exclude all organizations for which the first five characters are W.EP.
 
K

KJMachado

Thank you, John! I really appreciate the examples; it really clarified the
use of Like, Not Like and <> for me. Kathe
 
Top