Reputation: 1
I am using MS Access 2013 and I have a table where one of the fields is a dropdown that looks up values from another table. I am passing IDs to a SQL statement using a vba function and SQL IN() clause. It works with one value, when more than one value is passed it returns no records or a data type mismatch.
DIM varIDs AS String
VBA Function
getIDs(varIDs)
SQL Statement
SELECT ID, Name FROM Table
WHERE Table.ID IN (getIDs())
When varIDs = "26" it works When varIDs = "26, 27" it doesn't return anything or returns a data type mismatch
Been searching for an answers for 2 days and can't figure why its not working with multiple IDs being passed. Any help is appreciated
Upvotes: 0
Views: 78
Reputation: 112752
It does not work like this. The SQL is not expanded to WHERE Table.ID IN (26, 27)
as you might expect.
Change your VBA function to test for valid IDs instead
Public Function IsValidId(ByVal id As Long) As Boolean
IsValidId = id = 26 Or id = 27 'Replace by your implementation
End Function
Then change your SQL to
SELECT ID, Name FROM Table
WHERE IsValidId(Table.ID)
Another approach, if you can change the query dynamically (e.g. for a ComboBox), is to create it like this
Dim sql as String
sql = "SELECT ID, Name FROM Table WHERE Table.ID IN (" & getIDs() & ")"
cbo.RowSource = sql
Upvotes: 1