Prev: Displaying data across columns
Next: how to remove html tags from a field in MSACCESS.
From: Iram on 22 Apr 2010 13:12
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
From: Clifford Bass via AccessMonster.com on 22 Apr 2010 13:27
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

From: Bob Barrows on 22 Apr 2010 13:28
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


From: Clifford Bass via AccessMonster.com on 22 Apr 2010 13:29
Hi Iram,

Or:

Not Like "Item[1-2]"

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

Clifford Bass

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

From: KARL DEWEY on 22 Apr 2010 14:05
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
 |  Next  |  Last
Pages: 1 2 3
Prev: Displaying data across columns
Next: how to remove html tags from a field in MSACCESS.