CODEWITHSUNDEEP
sqlvb.netms-access
Shane LeBlanc
Shane LeBlanc

Reputation: 2643

Trying To Pass An Array Of Values To Search Using "IN"

I have a simple access query stored within Access and it does not seem to work. The query is this...

SELECT blah FROM table WHERE (((tableID) IN ([@myArrayOfIDs])));

Pretty simple but of course it just does not want to work? If I write IN (16,17,21) then the result is returned correctly but the way I have it now, nothing comes back at all. What am I missing?

Edit: I convert the array into a string of comma separated values using VB.NET before passing it to Access.

Upvotes: 1

Views: 1113

Answers (2)

HansUp
HansUp

Reputation: 97101

The Access db engine will not accept a parameter for the IN value list. IOW, something like this will never work ...

tableID IN ([a_parameter_string])

You would need to dynamically create the SQL statement and insert your string of comma-separated values into the SQL statement text.

Or you could do something like this instead ...

"," & [a_parameter_string] & "," Like "%," & tableID & ",%"

But that one seems ugly and will not be able to use indexed retrieval to speed up the query.

Upvotes: 1

koriander
koriander

Reputation: 3258

All you are getting is IN(['16,17,21']), it is not testing for the individual numbers. I don't recall Access supporting multiple variable number of parameters.

Upvotes: 1

Related Questions