CODEWITHSUNDEEP
sqlvbafunctionms-accessms-access-2013
userdz
userdz

Reputation: 1

Access SQL statement doesn't always work when passing parameters with a VBA function

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

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

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

Related Questions