Using Range w/ SQL statement in VBA

[supdawg]

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

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.

 

[Norie]

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.

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 & "'"

 

[supdawg]

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?

 

[supdawg]

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.