VB Macro to create SQL

[aka_krakur]

I have the following SQL: and I don't know how to have the VB Macro change the last parameter ((A_COMPL_SUMMARY.INCIDENT_NUMBER In (?))):

SELECT DISTINCT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, MDR_REPORTING.EVENT_DESC
FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.INCIDENT_NOTIFICATION INCIDENT_NOTIFICATION, CHSUSER.MDR_REPORTING MDR_REPORTING
WHERE MDR_REPORTING.CASE_NUMBER = A_COMPL_SUMMARY.CASE_NUMBER AND MDR_REPORTING.INCIDENT_NUMBER = A_COMPL_SUMMARY.INCIDENT_NUMBER AND MDR_REPORTING.PART_SEQUENCE = A_COMPL_SUMMARY.PART_SEQUENCE AND INCIDENT_NOTIFICATION.INCIDENT_NUMBER = MDR_REPORTING.INCIDENT_NUMBER AND INCIDENT_NOTIFICATION.INCIDENT_NUMBER = A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER In (?)))

I have a list from another worksheet that changes based off it's query results
Is there a way to have VB change the A_COMPL_SUMMARY.INCIDENT_NUMBER In .... to reflect a list found in the other worksheet?

 

[aka_krakur]

same thing... I'm gonna give up for the night.
Thanks for your help.
I'm gonna check back in the morning to see if I can figure anything else out.

 

[aka_krakur]

Got it to work...thanks EVERYONE for your help.
It ended up being a matter of a space before the end of one of the lines.
Since there has to be line continuations (still not understanding as to why there has to be)...but, on a couple of the ends of these lines I missed a space before the " which messed up the query SQL.
After much line by line verification I found the problems, fixed them and came up with the final VB (which works):

Private Sub Macro1()

 EndCell = Sheets("DEATH").Range("$A$65536").End(xlUp).Row
 If EndCell = 6 Then
  incidentrange = Sheets("DEATH").Range("A6")
  Else
 Sheets("DEATH").Select
 ActiveWorkbook.Names.Add Name:="Incident", RefersToR1C1:= _
        "=R6C1:R" & EndCell & "C1"

incidentrange = joinit(Sheets("DEATH").Range("Incident"), ",")
MsgBox ("Incident Range = " & incidentrange), vbOKOnly
End If

Sheets("Query2").Select
        
With Selection.QueryTable
        .Connection = "ODBC;DSN=VIPER TP10;UID=adhoc;PWD=viper01;SERVER=vprprd.world;"
        .CommandText = Array( _
        "SELECT DISTINCT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, A_COMPL_SUMMARY.PART_SEQUENCE, INCIDENT_NOTIFICATION.INST_NAME, A_COMPL_SUMMARY" _
, _
        ".NAME, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.CASE_OWNER, A_COMPL_" _
, _
        "SUMMARY.NAME, A_COMPL_SUMMARY.PRODUCT_FAMILY, MDR_REPORTING.INCIDENT_NUMBER, " _
, _
        "MDR_REPORTING.CASE_NUMBER, MDR_REPORTING.PART_SEQUENCE, MDR_REPORTING.REPORT_I_" _
, _
        "F_NO, MDR_REPORTING.EVENT_DESC " _
        & "FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.INCIDENT_NOTIFICATION " _
, _
        "INCIDENT_NOTIFICATION, CHSUSER.MDR_REPORTING MDR_REPORTING " _
& "WHERE MDR_REPORTING.CASE_NUMBER = A_COMPL_SUMMARY.CASE_NUMBER AND MDR_REPORTING" _
, _
        ".INCIDENT_NUMBER = A_COMPL_SUMMARY.INCIDENT_NUMBER AND MDR_REPORTING.PART_" _
, _
        "SEQUENCE = A_COMPL_SUMMARY.PART_SEQUENCE AND INCIDENT_NOTIFICATION.INCIDENT_" _
, _
        "NUMBER = MDR_REPORTING.INCIDENT_NUMBER AND INCIDENT_NOTIFICATION.INCIDENT_NUMBER " _
, _
        "= A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER " _
, _
        "In (" & incidentrange & ")))" _
& "ORDER BY A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE")

End With

Sheets("Query2").Range("C6").Select
Selection.QueryTable.Refresh BackgroundQuery:=True