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

Question

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

Hello jarlh, I'm not using an array, the IDs being returned is a string — userdz
– userdz, Commented Mar 12, 2019 at 20:06
You don't want to use a comma-separated string for IDs. You want to join on a temp table that contains the IDs you want. Does Access do temp tables? — Mathieu Guindon
– Mathieu Guindon, Commented Mar 12, 2019 at 20:19
In the SQL statement if I manually enter the IDs instead of the function it works fine — userdz
– userdz, Commented Mar 12, 2019 at 20:25
It does work. You just have no ideas like look like "26,27" (comma included. — Gordon Linoff
– Gordon Linoff, Commented Mar 12, 2019 at 20:33

Olivier Jacot-Descombes · Accepted Answer · 2019-03-12 20:33:29Z

1

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

answered Mar 12, 2019 at 20:33

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

userdz Over a year ago

Thank you all for taking the time to respond...@Olivier, I was able to get it to work using your alternate approach, I build the entire sql statement with the appropriate IDs and then I update the DAO, CurrentDB.TableDefs("Table").Fields("Field").Properties("RowSource") = sql Hoorah, worked like a charm!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related