Multiple "Not Like" not working

  • Thread starter Thread starter Iram
  • Start date Start date
I

Iram

Hello,

I need help writing a query that will exclude certain data.
I can successfully exlcude one item but when it comes to excluding multiple
I can't get the query to work properly.
If I wanted to exclude Item1 and Item2 from the query what is the proper way
to write it? The field name is Items and I have multiple items starting with
Item1 through Item150 but I only want to exlcude Item1 and Item2 from this
specific query.
I have written stuff like the following and they don't work...
Not like "Item1" and "Item2"
Not like "Item1" Or "Item2"
Not like "Item1"
Or
Not Like "Item2"



Thanks.
Iram/mcp
 
Iram said:
Hello,

I need help writing a query that will exclude certain data.
I can successfully exlcude one item but when it comes to excluding
multiple I can't get the query to work properly.
If I wanted to exclude Item1 and Item2 from the query what is the
proper way to write it? The field name is Items and I have multiple
items starting with Item1 through Item150 but I only want to exlcude
Item1 and Item2 from this specific query.
I have written stuff like the following and they don't work...
Not like "Item1" and "Item2"
Not like "Item1" Or "Item2"
Not like "Item1"
Or
Not Like "Item2"
Click to expand...

Why are you using Like? Without wildcards, Like does the same thing as
=. Assuming you decide you really should be using = here, then the easy
way is to do this:

WHERE ... NOT Items IN ('Item1','Item2')

which is the same as doing this:

WHERE ... NOT (Items='Item1' OR Items='Item2')

If you really do need to use wildcards and LIKE, then adapt the second
example, like this:

WHERE ... NOT (Items LIKE 'Item1*' OR Items LIKE 'Item2*')

This can also be written like this:

WHERE ... Items NOT LIKE 'Item1*' AND Items NOT LIKE 'Item2*'
 
If the exact content in your Items field is "Item1" or "Item2" then do not
use 'Like' function at all. Use this --
<>"Item1" AND <>"Item2"
 
Thank you Karl, Clifford and Bob for your responses.
Actually I was using Like because I didn't know what I was doing. I was
trying to explicitly exclude "Item1" and "Item2" in Access (not SQL)

Is this what I should use afterall?

<>"Item1" AND <>"Item2"



Thanks.
Iram
 
Thank you Karl, Clifford and Bob for your responses.
Actually I was using Like because I didn't know what I was doing. I was
trying to explicitly exclude "Item1" and "Item2" in Access (not SQL)

Is this what I should use afterall?

<>"Item1" AND <>"Item2"
Click to expand...

Just to clarify some jargon - SQL (Structured Query Language) is a generic
term for the language used in many different relational databases (DB2,
Oracle, SQL/Server, MySQL, and... yes... Access). It doesn't mean the
Microsoft product SQL/Server. All Access queries are stored as SQL, and the
query grid is just a tool to build a SQL string.

If you want to exclude a few specific items, the most efficient syntax is

NOT IN ("Item1", "Item2", "Item3")

If you have a large list of items to exclude, you may want to use a
table-driven solution instead - perhaps you could post your current table
structure and indicate just what you're trying to accomplish.
 
I was writing sql, haveing failed to notice that he was talking about
typing stuff into the Design grid.
Hi Iram,

Any of these is fine when entered into a criteria row in the
query designer--take your pick:

<>"Item1" And <> "Item2"

Not In ("Item1", "Item2") (note the correction of Bob's example)

Not Like "Item[1-2]"

Clifford Bass
Thank you Karl, Clifford and Bob for your responses.
Actually I was using Like because I didn't know what I was doing. I
was trying to explicitly exclude "Item1" and "Item2" in Access (not
SQL)

Is this what I should use afterall?

<>"Item1" AND <>"Item2"

Thanks.
Iram
Click to expand...
Click to expand...
 
Back
Top