Using Range w/ SQL statement in VBA

supdawg

Well-known Member
Joined
Mar 18, 2007
Messages
608
So I know almost nothing about SQL. I've been reverse engeneering a odc file that pulls a query from a database.

I have modified it to be able to pull specific columns out of the table. What I would like to do is use a range as my

Code:
PKMSID = InputBox("PKMS Login")
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};UID=" & PKMSID & ";SIGNON=1;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=Q" _
        ), Array("GPL WM0272PRDD;SYSTEM=PKMS0272.US.CORP;")), Destination:= _
        Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT PHPICK00.PHPKTN, PDPICK00.PDSTYL, PDPICK00.PDOPQT, PDPICK00.PDSTYD, PHPICK00.PHPSTF" & Chr(13) & "" & Chr(10) & "FROM CAPM01.WM0272PRDD." _
        , _
        "PDPICK00 PDPICK00, CAPM01.WM0272PRDD.PHPICK00 PHPICK00" & Chr(13) & "" & Chr(10) & "WHERE PDPICK00.PDPCTL = PHPICK00.PHPCTL AND ((PHPICK00.PHWH" _
        , "SE='BNA') AND (PHPICK00.PHPSTF=<'90') [B]AND (PHPICK00.PHPKTN IN ('677722691', '693205564'[/B])))")

The bolded part is where I would like to replace those two numbers with a range of numbers from an excel file.

Could be thousands of rows to look up. I know I could probably concantonate a string, but I would have to think there is an easier way to do this.

Just need to convert the range into a variable I can call from this code. Just not sure how exactly to do this.

Any help would be greatly appreciated. I am sure it's probably simple to those who are familiar with this.
 
Have you checked what the correct syntax for this is in the database you are querying?

Also, in the database, what's the datatype of the field you are setting the criteria for.

Here's one way you can create the list from a column of data.
Code:
Dim arrValues
Dim strValues As String

arrValues = Range("A1:A10")
 
arrValues = Application.Transpose(arrValues)

strValues = Join(arrValues, "X,X")
' if numeric
strValues = Replace(strValues, "X", "")
' if text
strValues = Replace(strValues, "X", "'")
 
strValues = "'" & strValues & "'"
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Thank you for the detailed explanation. I really appreciate the help.

So I've got it working. The issue was my end. The AND was just an "A". :D

So I've been playing with this. It works great, but I can't seem to do more than about 14 rows of data on it? Then I get a type mismatch error?

ANy ideas?
 
Upvote 0
I've been searching for an answer, but I don't see one. There seems to be a limit of items that can be used with the IN operator. That limit appears to be 14 different items in my range... :(

Everytime I try to use 15 values or higher, I get a type mismatch error.
 
Upvote 0

Forum statistics

Threads
1,224,524
Messages
6,179,308
Members
452,904
Latest member
CodeMasterX

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