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

From: John W. Vinson on 22 Apr 2010 18:37
On Thu, 22 Apr 2010 14:11:04 -0700, Iram <Iram(a)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]
From: Bob Barrows on 23 Apr 2010 08:37
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


From: Clifford Bass via AccessMonster.com on 23 Apr 2010 13:15
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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Displaying data across columns
Next: how to remove html tags from a field in MSACCESS.