VB Macro to create SQL

A

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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 (?))):
Code: 
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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
I do already have this set up in my VB:
Code: 
    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 (?)))
End With
End Sub
 
Upvote 0
I'm getting a lot closer...
If I manually change the ? to a true Incident Number(s) for instance:
77199,79110,79130,79204,79218,79318,80131,80695,80839,80839,80923,80923,81006,81296,83109,84809,84900,84975,85725,86823,88664,89896,91710,92980,93879,94349,95287

Now I just need to figure out what seems should be the most simple:
How do I define this list of #'s
I have them concatenated in a cell D2
 
Upvote 0
It seems like I should just be able to define something like:
IncidentRange = Range("D2").Value or Text or something

Then I should be able to edit
= A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER In (?))) to

"= A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER In ("& IncidentRange" &)))")

Please help me figure out how to define this IncidentRange = portion of this vba
 
Upvote 0
I came up with the following:

incidentrange = "Sheets("DEATH").Range("D2").Value

to test it out I added a
MsgBox ("Range = " & incidentrange), vbOKOnly

These gave me a message box Range = ...with a long list of values separated by commas (which is what I wanted). So this told me that the incidentrange name worked

I tried substituting as shown above:
"=A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER IN (" & incidentrange & ")))")

I'm back to getting the Run-time error '13':
Type mismatch

When I take out the incidentrange from that portion of the VB and physically copy/paste the list of numbers that are separated by a comma
(77199,79110,79130,79204,79218,79318,80131,80695,80839,80839,80923,80923,81006,81296,83109,84809,84900,84975,85725,86823,88664,89896,91710,92980,93879,94349,95287)
The entire VB works and puts all these numbers into the IN( ) portion of the SQL and then executes the query and returns the query results as expected.

I can't figure out why the incidentrange name doesn't work in this substitution...
Please, any ideas, help....PLEASE!!
 
Upvote 0
Where did you that error at?
In the function or your sql ?

OOps. vba code...

krakur(Range("INCIDENT"),",")
or
krakur(Sheets("YourSheetNameHere").Range("INCIDENT"),",")
 
Upvote 0
when i change this portion of my vb code
Code: 
        "= A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER In(" & krakur(INCIDENT,",") & ")))")

back to this:
Code: 
 "= A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER In(93142,93679,94100)))")

It works;
yet, if the results of krakur(INCIDENT,",") is 93142,93679,94100
this still won't work.
I've even tried defining that formula as IncidentRange = Range("D2").Value

and to test it out I added a MsgBox command that displays the results of krakur(INCIDENT,",") as 93142,93679,94100

so I know the formula itself is working and I know that by naming it IncidentRange in my VB is working due to the msgbox test

but for some reason it gives me this run-time error when I try and substitute in the formula or the VB Name "Incident Range"

I guess it's possible that in Passing the SQL via the VB, it won't allow anything but the real string.

I'm at a definite loss on this one.
been searching the internet pretty much all day for a solution too....no luck
 
Upvote 0
Where did you that error at?
In the function or your sql ?

OOps. vba code...

krakur(Range("INCIDENT"),",")
or
krakur(Sheets("YourSheetNameHere").Range("INCIDENT"),",")
Click to expand...

Have you tried?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
SQL for Oracle to use in MS Query
Replies
0
Views
375
aka_krakur
A

Forum statistics

Threads
1,214,945
Messages
6,122,397
Members
449,081
Latest member
JAMES KECULAH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top