CODEWITHSUNDEEP
sqlms-access
Justin Sun
Justin Sun

Reputation: 47

ACCESS SQL IN function with wildcard

How can I make Access treat *1* as a string instead of using wildcard in the SQL SELECT * FROM TABLE1 WHERE ID IN ("*1*","*2*");?

Upvotes: 0

Views: 739

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

In this expression:

WHERE ID IN ("*1*", "*2*")

MS Access does treat the values as strings and not wildcards. The wildcards are only used for LIKE.

If you want them to be treated as wildcards, you need to use LIKE. That would require OR:

WHERE ID LIKE "*1*" OR ID LIKE "*2*"

Or more simply as:

WHERE ID LIKE "*[1-2]*"

Upvotes: 1

daShier
daShier

Reputation: 2116

Sadly, this is a limitation of Access. You can use [*] to search for any single character, so:

    TABLE1 WHERE ID IN ("[*]1[*]","[*]2[*]")

Would match any single leading and trailing character with 1 or 2 in the middle. Not what you want, but closer.

Upvotes: 1

Related Questions