Multiple "Not Like" not working

[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

 

[Clifford Bass via AccessMonster.com]

Hi Iram,

Not sure why you want to use the like operator. That is usually used
when doing wild-card searches. In the Items column in your query just place
this:

<> "Item1" And <> "Item2"

In SQL it will look like:

... Where Item <> "Item1" And Item <> "Item2" ...

If you really want to use like:

Not Like "Item1" And Not Like "Item2"

... Where Item Not Like "Item1" And Item Not Like "Item2" ...

Clifford Bass

 

[Bob Barrows]

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"

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*'

 

[Clifford Bass via AccessMonster.com]

Hi Iram,

Or:

Not Like "Item[1-2]"

... Where Item Not Like "Item[1-2]" ...

Clifford Bass

 

[KARL DEWEY]

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"

 

[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"



Thanks.
Iram

 

[Clifford Bass via AccessMonster.com]

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

 

[John W. Vinson]

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"

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.

 

[Bob Barrows]

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

 

[Clifford Bass via AccessMonster.com]

Hi Bob,

SQL vs. designer was not what I was addressing. Rather it was the "Not
Items In..." vs. the "Items Not In...". It is not the normal construction of
the clause. However as I have thought on it I realized that is may work; and
so tried it. It did work. Very interesting!

Thanks,

Clifford Bass

 

[SQLGal via AccessMonster.com]

((not like "item 1") and (not like "item 2"))

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