Error 5 with SQL Query in VBA with Long "In" Clause

Question

I am building a macro that includes a SQL statement. The SQL statement references a cell for its "in" clause (cell G5 in the code below). If I reference two or three item numbers in this cell, the query executes as expected, but when I put a lot of item numbers (around ten or so), the query stops working and I get error 5: invalid procedure call or argument. Does anybody know how to work around this?

Here's the SQL query I'm trying to run... please let me know if you want the full VBA code for troubleshooting, but I'm cutting down to just the SQL and cell reference definition for brevity.

Dim myval As String
myval = Range("g5").Value
"select c.item_num, avg(s.ext_net_sls_amt/s.ordr_qty) as ASP" & Chr(13) & "" & Chr(10) & _
"from sls_tbl s" & Chr(13) & "" & Chr(10) & _
"inner join cust_tbl c on s.item_ID = c.item_ID" & Chr(13) & "" & Chr(10) & _
 "where c.item_num in (" & myval & ") and s.ordr_qty <> 0" & _
 "and s.invoice_dt between 20200723 and 20201023 group by c.item_num")

question from:https://stackoverflow.com/questions/65941137/error-5-with-sql-query-in-vba-with-long-in-clause

Answer 1

Waitting for answers