Multiple "Not Like" not working

  • Follow


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
0
Reply Utf 4/22/2010 5:12:06 PM

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

Iram wrote:
>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

-- 
Message posted via http://www.accessmonster.com

0
Reply Clifford 4/22/2010 5:27:45 PM

Iram wrote:
> 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*'



-- 
HTH,
Bob Barrows


0
Reply Bob 4/22/2010 5:28:52 PM

Hi Iram,

     Or:

Not Like "Item[1-2]"

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

        Clifford Bass

-- 
Message posted via http://www.accessmonster.com

0
Reply Clifford 4/22/2010 5:29:55 PM

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"


-- 
Build a little, test a little.


"Iram" wrote:

> 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
0
Reply Utf 4/22/2010 6:05:01 PM

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


"Bob Barrows" wrote:

> Iram wrote:
> > 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*'
> 
> 
> 
> -- 
> HTH,
> Bob Barrows
> 
> 
> .
> 
0
Reply Utf 4/22/2010 9:11:04 PM

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

Iram wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

0
Reply Clifford 4/22/2010 9:45:20 PM

On Thu, 22 Apr 2010 14:11:04 -0700, Iram <Iram@discussions.microsoft.com>
wrote:

>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.

-- 

             John W. Vinson [MVP]
0
Reply John 4/22/2010 10:37:24 PM

I was writing sql, haveing failed to notice that he was talking about
typing stuff into the Design grid.

Clifford Bass via AccessMonster.com wrote:
> 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
>
> Iram wrote:
>> 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

-- 
HTH,
Bob Barrows


0
Reply Bob 4/23/2010 12:37:07 PM

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



Bob Barrows wrote:
>I was writing sql, haveing failed to notice that he was talking about
>typing stuff into the Design grid.

-- 
Message posted via http://www.accessmonster.com

0
Reply Clifford 4/23/2010 5:15:34 PM

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




Iram wrote:
>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

-- 
Message posted via http://www.accessmonster.com

0
Reply SQLGal 4/23/2010 10:45:27 PM

10 Replies
5587 Views

(page loaded in 0.142 seconds)


Reply: