Multiple "Not Like" not working

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
Utf
4/22/2010 5:12:06 PM
access.queries 6343 articles. 1 followers. Follow

10 Replies
23316 Views

Similar Articles

[PageSpeed] 36

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
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
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
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
1
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
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
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
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
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
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
SQLGal
4/23/2010 10:45:27 PM
Reply:

Similar Artilces:

Custom Receipt problem
I have modified the Full Page Invoice from CustomerSource. My receipt is a half-page (5.5 x 8.5) format. I have also added the following to the bottom of 'Bill To & Ship To' part of the header area: <IF> <CONDITION>Transaction.Type = transactionWorkOrder</CONDITION> <THEN> <ROW>"Reference:|" Transaction.ReferenceNumber </ROW> </THEN> </IF> <IF> <CONDITION>Transaction.Type = transactionWorkOrder</CONDITION> <THEN> <ROW>"Comment:|" Transaction.Comment </ROW> </THE...

Not Like 08-29-07
I have a number field in my query and I need the criteria to be... Not Like 6 Not Like 9 Not Like 10 Not Like 11 It can't be like any of the above...but the 6 is throwing me off. How can I do this? Thanks DS The Like expression is normally used for setting criteria for text fields, usually in combination with (a) wildcard character(s). To exclude any of the (integer) values 6, 9, 10, or 11, you can simply set a criterion of: Not In (6, 9, 10, 11) HTH Rob "DS" <bootybox@optonline.net> wrote in message news:Fn2Bi.29$vl.4@newsfe12.lga... >I have a number field...

Custom Receipt problem
I'm posting this again because apparently the first message doesn't qualify for my managed newsgroup benefit. ---Original question--- I have modified the Full Page Invoice from CustomerSource. My receipt is a half-page (5.5 x 8.5) format. I have also added the following to the bottom of 'Bill To & Ship To' part of the header area: <IF> <CONDITION>Transaction.Type = transactionWorkOrder</CONDITION> <THEN> <ROW>"Reference:|" Transaction.ReferenceNumber </ROW> </THEN> </IF> <IF> <CONDITION>Transaction.Typ...

in Version 2002 ,something not like c++
in Version 2002 ,something not like c++,not support user's class adding,If I Add,must go to the code page to add! ...

Outlook not like it was after a reinstall.
Outlook is Different after Reinstall. Before I slammed this box/computer, everything was running quite well. The reason for the slam, the scanner, the computer though it was unavailable. Not true, so that is why I slammed it. I am running Windows 2000 Pro. After the slam, 42 updates and at least 10 required a reboot. Long day. But otherwise it appears to be running quit well including the scanner. There was a period of time when Internet Explorer wouldn't show certain web-sites, yet it would show others. Can't recreate that one. Only happened short time during two nights a week ago n...

Not Like 12-05-07
This isn't returning a value. I think its the combination of two different Not In and Not Like. Any help appreciated. Thanks DS SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName FROM tblTabTypes WHERE (((tblTabTypes.TabTypeNumber) Not In (6,9,10,11) And (tblTabTypes.TabTypeNumber) Not Like [Forms]![frmFXCheckType]![TxtTypeID])) ORDER BY tblTabTypes.TabTypeName; Is TabTypeNumber a text field or is it a number field? You are treating it as both in your code. Access will attempt to convert a Number to a string if you use the Like operator. What is the Value of txtTypeID? If ...

Multiple "Not Like" not working
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 "...

Not like or Not In?
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 "pgarcia" <pgarcia@discussions.microsoft.com> wrote in message news:A6968964-A57C-4FCB-92A9-AD7FDB5CDB3F@microsoft.com... > I'm having a problem with the following: > Not Like ("BAX") > Not In ("BAX") > > The abov...

What if I do not like IE8?
Short version of question: How do I uninstall IE8 if I have problems with it like other users have had in user reviews? How do I revert back to IE7? I am using IE7. I want to upgrade to latest IE8. I read the user reviews at Download.com and many users have no problems with IE8 and many late posting users have dreadful problems with IE8. When trying general software, if you do not like it, uninstall it. The unique problem with IE8 is the assumption that it cannot be uninstalled and revert back to IE7. Do I dare install IE8? What if I do not like it or have problems w...

Not Like function
Hi There I trying to exclude two data in my queries using not like function. The output is as below; If I'm using WHERE (((([tbl_[NAME])) Not Like "HA1")) OR (((([tbl_[NAME])) Not Like "HM1")), both HA1 and HM1 still exist in the output. If I'm using WHERE (((([tbl_[NAME])) Not Like "HA1")) OR (((([tbl_[NAME])) = "HM1")) only HA1 are exluded. can you guide me please how can I do the right queries to exclude both HA1 and HM1 thank you -- Cheers Mohsin Message posted via AccessMonster.com http://www.accessmonster.com/U...

query filter not like
I have a field that contains 6 digits, then an astrik, then 3 digits, then an astrik , then possible 2 digits, then an astrik and 1 digit. I need to filter for all but the records that contain the last astrick and 1 digit. I have tried "Not Like "######" & "*" & "###" & "*" & "#" & "*" & "#" - but no luck - can anyone help ? How about another tack. Seems that you want fields with less than 14 characters. Put something like this in the Field of the query. TheLenght: Len([TheFieldName) I...

HOW TO USE NOT LIKE EXPRESSION
How to not select certain multiple criteria. I want to not select certain contractor codes. I tried the not like function but can't get it to work on not selecting multiple contractors. I tried to setup the formula like this: Not like "Vic" or not like "Pioneer" or not like "Bud" but this does not work. How do I setup an expression to not select VIC, PIONEER OR BUD (AND MANY OTHER CONTRACTORS)? Thanks "Vic" NOT LIKE "Vic" OR "Vic" NOT LIKE "pioneer" evaluates to False OR ...

not like syntax in querry not working
Hello all- I am pulling data from an access dbase and have ran into a syntax issue. Does anyone what is wrong with the last line? The data pulls, but ignore the not like statements and i get all the items with gfm and cfm in the material name along with the data im looking for. I need to exclude all records with GFM or CFM in the material field. Please help strsql = "SELECT PEGG_TASSRAW.Plnt, PEGG_TASSRAW.Material, CJI3ByReplen.Description, CJI3ByReplen.PurchDoc, PEGG_TASSRAW.[Replenishment Element], PEGG_TASSRAW.[Grouping WBS el], PEGG_TASSRAW.[Assigned WBS], PEGG_TA...

Not Like
I have several thousand rows of data with various values (CL, A1, A2, Null). When I put the criteria - Not Like "CL" in the query criteria, it doesn't return any rows (and I know there are rows that don't have "CL" in them). If I put Is Null or Not Like "CL", it will return the blank rows. I want to return the other values (A1, A2 and Nulls), but I can't get it to return any records when I just say Not Like "CL" or Not In ("CL"). This should be simple, and I'm sure I'm overlooking something....any help is app...

NOT LIKE '*[a-z]*'
Hi I am trying to make a Query. I will populate suspected corrupted data from a Table with a single Field which contains email addresses stored as text. How can I populate the data which contains only: NOT LIKE "abcdefghijklmnopqrstuvwxyz123456789@._-"? (A standard Query with NOT LIKE '*[a-z]*' is not working. Do I need to make a SubQuery or a Funtion do deal with this ?) JohS I can't see how this is going to work. I have these data in tblContacts (only two of them is not corrupted emails in this example - and I want to populate only the corrupted) : ab abc a...

And v. Or and Not Like for in two fields
Hello, I have a field in a patient table called PrimaryDiagnosisCode which has a five character value that is either numeric or alpha numeric. I also have a field called AllSecondaryDiagCodes which has these same five character values but can have several of them all separated by semi-colons. For instance, a value can look like the following: ;25002;99760;4439;2809;V5867;V6284;412;496;41400;2 I have been given a list of codes to find in either field (Primary or secondary) along with exception codes for the secondary. For instance, if the code 64880 appears in the seconda...

2007 does not like 2003 chart macros
I have a lot of time invested over the years in macros to analyze data from my work. I read and graph various files of data that I assemble into an Excel sheet. Everything works perfectly in 2003. But in 2007, I can not reliably generate graphs that include all my data. I have observed at least 2 repeatable problems as follows: 1. I can generate a "base" graph with up to 4 data series. If I then run a macro to change each series line color and weight, I will loose the 4th series. The data is there and the series shows up on on the graph key, but there is no visible line...

Not LIKE [pattern] criteria includes NULLs?
The subject of this post basically outlines the question. If I use aNot LIKE [pattern] under criteria for a select query, will the querypick up records with a NULL value?...

SBS08 new install observations, not like all my other installs
Well, setup the new domain and added the old workstations to the new domain, some strange observations: The SBS admin account, when joining computers to the domain via //connect, it would not give me permission to add this computer for other users like all my other SBS installs have, had to add each one as FOR MYSELF and then go back and add the users manually.... From the User wizard, if I make 5 users local admins on a machine, from the machine itself they are not added to the local administrators groups, even after 24 hours. GPO to force screen saver to ssbez and 5 minu...

telephone no. in invoice address-Post Office does not like
Post Office has commented about the recepient phone number under the city, state ZIP of the invoice address. Apparently this does not conform to Postal Regulations. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/N...

Using 'Not Like' for exact string matches
Is there a performance difference between these two query criteria statements? Not Like "aa" and Not Like "bb" vs. Not "aa" and Not "bb" I've seen where you should only use Like when a wildcard is being used. I'm looking to speed up these queries any way I can. Thanks in advance. I believe that you will see no difference in performance for the two queries. Have you indexed the field that you are applying the criteria to? Indexing will usually make query performance quicker. "Usually .. quicker" meaning that if you are adding r...

"Not like" doesn't work in a form
I'm puzzled and suspect I'm missing something simple, but can't figureit out. Searching the group has led to no enlightment.I'm doing an if/else statement. If I use the following statement, itworks:if me.txtline Like "1010*" thenbla bla blaend ifIt finds the record where the field has a value similar to 1010.If OTOH I want to find the values that are NOT like 1010, it doesn'twork.I tried:me.txtline not like "1010*"andme.txtline <> "1010*"What am i missing?Thanks in advance I usually phrase it like this:If me.txtline Like "1010*&quo...