Problems with wildcards in SQL Where IN Clause

[ArtPM]

I'm having problems getting wildcards to work in the Select Where In clause. All text inputs work fine; wildcards alone or combined with text do not.
Here's the statement:
Q.SQL = "(SELECT * FROM Combined_AR_Table_withAllDept WHERE (((Combined_AR_Table_withAllDept.C2PComplex) In (" & Criteria1 & &quot) AND ((Combined_AR_Table_withAllDept.[Resources Query_1_DeptName]) In (" & Criteria & &quot) AND ((Combined_AR_Table_withAllDept.[Status]) In (" & Criteria2 & &quot))"

 

[Steve101]

The IN clause does not support wildcards; it will try to do exact matching (as opposed to pattern matching) and will thus fail.

You may be able to use the LIKE operator to achieve what you want; depends on your requirement. If you publish some sample data, we'll be able to help you further.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[ArtPM]

Thanks Steve. I suspected that this was the case, but could not find it documented anywhere.

The situation I am trying to solve is 3 multi-select listboxes, in which I would like to have one entry that selects all items in the list rather than having to manually select all.

Thanks for your help!