How to execute stored query with parameters and "IN" clause? 1

[vladk]

Hi,

Can anybody help me with "IN" clause parameters in a stored query?

My insert qryMyAmts query contains such a clause:

WHERE MY_AMTKEY IN ('1','2','3')

It runs fine.

I want to call it from VBA in Access and I changed the clause to

WHERE MY_AMTKEY IN ([MyAdmKeyList])

My VBA code is like this:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryMyAmts")
qdf.Parameters("MyAdmKeyList").value = strMyAdmKeyList
qdf.Execute

Where for strMyAdmKeyList I tried to assign this: "'1','2','3'" and this: "1,2,3".
In both cases the query inserts nothing. However, if the list contains just one value, it works.

May be somebody knows how to prepare such a parameter?

Thank you!

vladk

 

[PHV]

I'd try something like this:

Dim qdf As DAO.QueryDef
Dim strSQL As String
Set qdf = CurrentDb.QueryDefs("qryMyAmts")
strSQL = Replace(qdf.SQL, "[MyAdmKeyList]", strMyAdmKeyList)
DoCmd.RunSQL strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vladk]

Hi PHV,

Your advice works just fine!

Thank you so much!!!

vladk