Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

08211987

Programmer
Apr 20, 2012
187
US
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!
 
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.
 
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.
 
Thanks! How to code the syntax for if it is NOT = or In
 
Never mind I've got it - Not "name" And Not "name"
Thanks!
 
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
 
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.
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top