I doubt it because the database engine can't resolve the array (you could do it for a single value from the array). You could loop through the array and build a comma delimited string variable, then:
I'm on the side of possible because this was posted in vba forum. If it was posted in queries (assuming there is such a forum) I'd say no. Should be an interesting outcome...
I'm going to give the comma delimited string a try, I did try it the other way but it didn't work. I think that if we design the string to properly function like the WHERE clause does and then add the two strings together it should work.
I'm going to give the comma delimited string a try, I did try it the other way but it didn't work. I think that if we design the string to properly function like the WHERE clause does and then add the two strings together it should work.
I/we must see that phrase 100x per month and yet it is of zero help. Maybe all you did wrong was not wrap quotes around text. This suggests to me that i is a string, but you have not delimited it.
WHERE [Tools].[ToolID]=" & arrTooling(i) & ""
I interpreted the question as wanting to filter on all values in the array, not a specific one. I don't think you could pass an array and filter on all values in the array without looping it into a string. I agree you could filter on a single value like the example, which is why I added "you could do it for a single value from the array".
Something to remember about a WHERE clause, it can only see what is actually passed in the WHERE. An array in memory would reside on the computer it was created on. As the server itself may be on a different machine that machine would not normally be able to see the contents of an array directly. As such you need to either pass the server a complete text string of what you want to match to OR reference a query/table that can be joined to.
While your array itself can't be joined to, is there an underlying query or table that you can join to?
Building the In() clause would have been better. Think about the difference between these two results:
Where somefield In("a, b, c")
and
Where somefield in("a", "b", "c")
In the first case, you have one string with a value = "a, b, c" which is unlikely to match anything. In the second case, the In() contains THREE separate strings.
That is why you cannot do this using parameter substitution in a saved querydef but you can do it if you build the Where clause using VBA.
Here's an example of how to build the string:
Code:
Me.txtSelectedSequences = Null
For Each i In Me.lstSequence.ItemsSelected
strIN = strIN & QUOTE & Me.lstSequence.ItemData(i) & QUOTE & ","
Next i
If strIN & "" = "" Then
Me.txtSelectedSequences = Null
Else
strIN = Left(strIN, Len(strIN) - 1) ' remove trailing comma.
Me.txtSelectedSequences = strIN
End If
To make the code easier to read and not have bunches of "'s in packs, I use a constant named QUOTE whenever my intention is to insert into a string a single ".
