criteria to find values In a list or names OR Not in a list of names - "data type mismatch erro 1

[08211987]

I have a table with a field

.[LOBManager] and I need 2 queries where in 1 I need find out if the record has one of these values and another where the record does not contain any of the values.
Values = “Cheri West", "John Smith", "Peter Brown, III"

I tried:
In “Cheri West" Or "John Smith" Or "Peter Brown, III"
The SQL showed this:
In(([Table1].[LOBManager])="Cheri.West" Or ([Table1].[LOBManager])="John Smith" Or .... for the 3rd name it split it out into 2 names, one being the III.
And bottom line I get an error "data type mismatch in criteria expression". The field name fields are short text.
I'm sure the name with III will probably need a &?
Is there another way to do this beside "In"?
And how would I code it as Not In?
Appreciate the help!

 

[08211987]

Forgot to mention that if names are in the list or are not in the list, I will be setting another field in the table to a certain value.

 

[Andrzejek]

If your Table1 has those values in the field:
[tt]
LOBManager
...
Cheri West
John Smith
Peter Brown, III
...
[/tt]
then your syntax would be:
[tt]
Where Table1.LOBManager IN ("Cheri.West", "John Smith", "Peter Brown, III")
[/tt]
or (which is the equivalent of)
[tt]
Where Table1.LOBManager = "Cheri.West"
OR Table1.LOBManager = "John Smith",
OR Table1.LOBManager = "Peter Brown, III"
[/tt]

But not both: OR and IN

---- Andy

There is a great need for a sarcasm font.

 

[08211987]

Thanks! How to code the syntax for if it is NOT = or In

 

[08211987]

Never mind I've got it - Not "name" And Not "name"
Thanks!

 

[08211987]

Actually, this is what Access changed it to in the query:
<>"Mark tests" And <>"Bruce dfsdf" And <>"Cheri dfdhffh" And <>"John jgfjgjfgo" And <>"William fghfghr, III

 

[Andrzejek]

It would be nice to see the whole Select statement that you are using (or Access suggests). We may find a better / simpler way to re-write the statement to get what you want.


---- Andy

There is a great need for a sarcasm font.

 

[08211987]

Andy, I believe it is giving me what I need, I will check it again in the morning - it is on my work laptop. Thanks for all your help!