troubleshooting Question

Passing a string list parameter to IN statement

Avatar of bishopkd
bishopkdFlag for United States of America asked on
Microsoft Access
1 Comment1 Solution428 ViewsLast Modified:

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.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros