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.

 

[Colin Legg]

Why not write a little function which accepts a range and returns a string (such as '677722691', '693205564') ready to be concatenated into your SQL query?

Function GetInClauseFromRange(ByVal rngSource As Range) As String
 
 
End Function

The function will have to iterate through the cells in the range, putting single quotes around each value to produce the final output. For improved performance you can dereference the range like this:

dim vValues As Variant
 
vValues = rngSource.Value

If rngSource is more than one cell then vValues will be a 2-D array. It's faster to loop through an array than a range.

 

[supdawg]

Hi Colin, thanks for the reply. I've been searching for a solution. I found a post on this site and it gave me a handy function:

Function MakeSQL(rng As Range) As String
 
  Dim oCell As Range
  
  For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
  Next oCell
  
  MakeSQL = "IN ( " & Mid(MakeSQL, 2) & " )"
  
End Function

http://www.mrexcel.com/forum/showthread.php?t=530906

But I tried to get that to work in my code, but I couldn't get the syntax right.

I think it may work? Just need to figure out how to use this function with the code I posted above? Sorry, not very good at VBA I admit. I tried a couple different things and I kept getting errors.

 

[Colin Legg]

Okay, let's use that function you found as a basis. It's similar enough to what I suggested.

Let's suppose that Sheet1!A1:A2 contains the values 677722691 and 693205564. The last line of your code then becomes:

 , "SE='BNA') AND (PHPICK00.PHPSTF=<'90') AND (PHPICK00.PHPKTN " & MakeSQL(Sheet1.Range("A1:A2")) & "))")

 

[supdawg]

Getting closer:

In the locals window, I see that the MakeSQL = "IN ( , '677722691' , '693205564' )"

Should there be a comma in the first part?

When I try to run as is, I get SQL Syntax Error... Is the function not correct?

 

[Colin Legg]

Yeah, there shouldn't be a comma at the start - it should read in the local window:

MakeSQL = "IN ('677722691' , '693205564' )"

This is a result of a small mistake in the function you posted. Have a look at it and see if you can spot what it is.

 

[supdawg]

Ughh...

I am so terrible at noticing small errors in code.

I know there shouldn't be a beginning comma in the function and it should after the value, however, I don't know how I am supposed to end it without leaving an extra comma after the last item in the range. :/

If you could kindly point it out to me, I'd be very happy. I could spend 30 minutes here trying different things before I figured it out. Getting those commas, parantheses, and quotes straight is soooo hard for me for some reason.

This is what I tried... added the comma at the end.

MakeSQL = MakeSQL & " '" & oCell.Value & "', "

 

[supdawg]

Is it the mid function?

I was able to get it to this:

"IN ('677722691', '693205564')"

But still get SQL error.

 

[Colin Legg]

Sure, no problem, I thought I'd let you have a crack at it first. So let's have a look at what the function does:

Function MakeSQL(rng As Range) As String
 
  Dim oCell As Range
 
  For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
  Next oCell
 
  MakeSQL = "IN ( " & Mid(MakeSQL, 2) & " )"
 
End Function

Function MakeSQL(rng As Range) As String

The function is called MakeSQL. It receives a Range and it returns a String.

Dim oCell As Range

It uses one variable, which is a Range variable called oCell.

For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
Next oCell

The function loops through each cell in rng, appending each cell's value (surrounded by a few other characters) to the end of a string. Actually, to be sure that it loops through cells (not rows or columns), the first line should really be:

For Each oCell In rng.Cells

Let's suppose that rng is three cells containing the values 1, 2 and 3. Once the loop has finished, MakeSQL will hold this value:

 , '1' , '2' , '3'

Note that there is a space at the front before the first comma.

The next line of code is as follows:

MakeSQL = "IN ( " & Mid(MakeSQL, 2) & " )"

Let's break this into bit size pieces.

Mid(MakeSQL, 2)

The Mid function returns characters from the middle of a string. The 2 indicates the starting character. So what this does is it effectively lops off the first character of MakeSQL; MakeSQL becomes this:

, '1' , '2' , '3'

There is now no space before the first comma.

The rest of the code line just wraps a few characters around it - "IN ( " at the start and " )" at the end - so it becomes:

IN ( , '1' , '2' , '3' )

So the trouble is that the Mid function is failing to remove the first comma. SQL doesn't need these spaces, so a simple fix is to remove the space here:

For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
Next oCell

---->

  For Each oCell In rng
    MakeSQL = MakeSQL & ", '" & oCell.Value & "'"
  Next oCell

Now, when the Mid function lops of the first character it will remove that comma. Make sense?

 

[Colin Legg]

Is it the mid function?

I was able to get it to this:

"IN ('677722691', '693205564')"

But still get SQL error.

Correct. It was the Mid function.

Post the code you're using now and we'll have a look at it...