Using "IN" in a query

[slimjen]

All, using Access 2010. I've been trying to use the IN clause in a query and not returning any results. I have a ID Code field that contains a State: "RepNJ" or "TamCA" etc. but when I try to use the IN I don't get results unless I put the whole ID Code Ex: IN("RepNJ","TamHI", "TamCA") I was trying to use a wildcard to return ID codes because there are so many. EX IN("Rep*","Tam*","CAP*") but this doesn't return any results. Can you not use a wildcard when trying to use "IN"??

[pbaldy]

My gut feeling is no. You need to use Like to use a wildcard, and an IN() clause converts to =. In other words:

FieldName IN("RepNJ","TamHI", "TamCA")

is the same as

FieldName = "RepNJ" OR FieldName = "TamHI" OR FieldName = "TamCA"

and a wildcard wouldn't be recognized in that.

[slimjen]

Ok. thanks

[pbaldy]

No problem.

[Micron]

I was trying to use a wildcard to return ID codes because there are so many.

Are there enough that you would entertain code? You could build a sql statement by looping through a recordset or an array and concatenating LIKE with OR

[orange]

You could put your "data" into a separate table and do a join

[JoeM]

I was trying to use a wildcard to return ID codes because there are so many. EX IN("Rep*","Tam*","CAP*") but this doesn't return any results. Can you not use a wildcard when trying to use "IN"??

Depending on the nature of your data, and whether your example is truly indicative of the rest of your data, you might be able to do something like this:

WHERE Left(FieldName,3) IN ("Rep","Tam","CAP")