Parameter Using In List In SQL Qualifier

[CAPETOWNANDRE]

Hi

I am using VBA to execute access queries, all works fin but cannot pass in more that one value in my parameter#

Select * from table where myvalue in ([myparm]);

this workd great for a single value : value01
But will not accept : value01,value02 ,Value03

I have tried many combinations of single and double quotes but with no sucess

[ADezii]

Hi

I am using VBA to execute access queries, all works fin but cannot pass in more that one value in my parameter#

Select * from table where myvalue in ([myparm]);

this workd great for a single value : value01
But will not accept : value01,value02 ,Value03

I have tried many combinations of single and double quotes but with no sucess

SELECT *
FROM Employees
WHERE Employees.Region In ('NJ','PA','WA');

[CAPETOWNANDRE]

Yep, that will work , but when you want to send the values as a parameter
MYPArm = " 'NJ','PA','WA ' "

select * from Employees where employees.regio n in ([myparm]);

It wont work

[ADezii]

Yep, that will work , but when you want to send the values as a parameter
MYPArm = " 'NJ','PA','WA ' "

select * from Employees where employees.regio n in ([myparm]);

It wont work

I tested the following code that I created and it does, in fact, work. Is this similar to what you are looking for?

Dim MyDB As DAO.Database
Dim rstTest As DAO.Recordset
Dim strSQL As String
Dim strParam As String

strParam = "'NJ','PA','WA'"

strSQL = "SELECT *FROM Employees WHERE Employees.Region In (" & strParam & ");"

Set MyDB = CurrentDb
Set rstTest = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

With rstTest
  Do While Not .EOF
    MsgBox ![FirstName] & " " & ![LastName]
    .MoveNext
  Loop
End With

rstTest.Close
Set rstTest = Nothing