Using NOT to exclude records in query design (1 Viewer)

[uplink600]

I'm using the NOT expression to exclude certain records in a query. I can't seem to get it working using more that one exclusion. For example I would like to return records for all sales people excluding............

Dave Smith
Alan Jones
Alice Carter

Please advise how this works in query design.

Thanks

Vere

 

[The_Doc_Man]

When you do your query grid, before you save it, switch to SQL to see how it makes the list.

Let's say your name field is called WholeName and that your spelling was exactimundo on the names. (Spelling of the individual names must be exact for this to work anyway.)

The part of the SQL that you want to see is

..."WHERE ( [WholeName] <> "Dave Smith" ) AND ( [WholeName] <> "Alan Jones" ) AND ( [WholeName] <> "Alice Carter" ) ;"

If you see OR instead of AND in such a clause, edit the SQL to correct the text to AND. Then switch back to the query grid to see what it did to you. Don't bother to save anything until you have the AND in place rather than the OR in the SQL view of the query.

Let me guess... you tried this in a query grid, listed the names, and nobody got excluded, right? That is because the query grid is implicitly based on OR, not AND - but in this case you really wanted AND, not OR, so the query grid fooled you.

 

[KenHigg]

Not like "Dave Smith" and Not like "Alan Jones" not like "Alice Carter"


???
ken

 

[Mile-O]

Not In("Name1", "Name2", "Name3", "etc.")

 

[KenHigg]

Not In("Name1", "Name2", "Name3", "etc.")

Cool.

.

 

[WayneRyan]

uplink,

Another variation:

Select SomeName
From   Table1
Where  SomeName Not In (
   Select SomeName
   From   Table2)

Wayne

 

[EmilyTheChef]

Let me guess... you tried this in a query grid, listed the names, and nobody got excluded, right? That is because the query grid is implicitly based on OR, not AND - but in this case you really wanted AND, not OR, so the query grid fooled you.

THANK YOU The_Doc_Man - AND vs OR... brilliant! Solved a problem I've been :banghead: over for a few weeks.