In VBA, I am attempting to pass a parameter as a list of strings to query IN statement.
Say that qry12 = SELECT Vendor, Sum(Cost) FROM tblMain WHERE tblMain.Vendor IN ([Enter Vendor])
And my code uses a querydef like this:
Set objQD = objDB.QueryDefs("qry12")
objQD.Parameters("[Enter Vendor]") = strTopVendors
Set objRS = objQD.OpenRecordset()
I have attempted to pass strTopVendors parameter in all of the following quoted and unquoted forms:
= Vendor1, Vendor2, Vendor3
= "Vendor1, Vendor2, Vendor3"
= 'Vendor1, Vendor2, Vendor3'
= "Vendor1", "Vendor2", "Vendor3"
= 'Vendor1', 'Vendor2', 'Vendor3'
The query runs fine, except that it looks for a single Vendor named Vendor1,Vendor2,Vendor3 and obviously doesn't find it. So my syntax is wrong. Please help with the quotes and commas? Or can you not pass a string list as a parameter in this way?
Many thanks in advance.