Type Mismatch Error using SQL statement w/ IN Operator inside VBA?

[supdawg]

As a continuation from my first thread located here:

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

My original issue was solved, but I ran into an unexpected problem which I do not know the answer to. I've done some searching, and there shouldn't be a limit on using an IN operator in SQL.

Code in original thread works, but only when using up to 14 values (in the range I specified). When I try to use this code on a range of values >=15, i get an type mismatch error?

I understand that generally a type mismatch error means it ran into an unexpected data format. I just don't understand what is wrong.

First, function that builds the string to be used with the SQL statement:

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

Secondly, the code:

Sub Macro1()
' Macro1 Macro
Dim PKMSID As String
Dim LastRow As Long
Dim wb As Workbook
Dim ws2 As Worksheet
Set wb = ThisWorkbook
Set ws2 = wb.Sheets("sheet2")
LastRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
 
    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=PKMS2.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') AND (PHPICK00.PHPKTN " & MakeSQL(ws2.Range("A1:A14")) & "))")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "TableDemand2"
        .Refresh BackgroundQuery:=False
    End With
 
End Sub

Any help fixing this or telling me this is a limitation of how I am doing it would be really apprecaited!

 

[Norie]

Did you check the data types as I suggested in the other thread?

I also posted code there to deal create the value of lists for both numeric an text values.

The only time I get the mismatch is when I use the wrong data type.

If it definitely is a text field in the database it could be that single quotes are needed not double quotes.

 

[xenou]

^^ good advice. Also I don't see anything in your SQL per se but it always helps to debug.print it for a better look. You can also take the output and run it directly on your database - error messages may be more helpful that way.

Here's how I'd set it up to get the statement:

strSQL = ""
strSQL = strSQL & "  SELECT PHPICK00.PHPKTN," 
strSQL = strSQL & "  PDPICK00.PDSTYL," 
strSQL = strSQL & "  PDPICK00.PDOPQT,"
strSQL = strSQL & "  PDPICK00.PDSTYD,"
strSQL = strSQL & "  PHPICK00.PHPSTF"
strSQL = strSQL & "  FROM"
strSQL = strSQL & "  CAPM01.WM0272PRDD.PDPICK00 PDPICK00,"
strSQL = strSQL & "  CAPM01.WM0272PRDD.PHPICK00 PHPICK00"
strSQL = strSQL & "  WHERE"
strSQL = strSQL & "  PDPICK00.PDPCTL = PHPICK00.PHPCTL"
strSQL = strSQL & "  AND"
strSQL = strSQL & "  ("
strSQL = strSQL & "  	(PHPICK00.PHWHSE='BNA')" 
strSQL = strSQL & " 		AND"
strSQL = strSQL & " 		(PHPICK00.PHPSTF<='90')" 
strSQL = strSQL & " 		AND"
strSQL = strSQL & " 		(PHPICK00.PHPKTN " & MakeSQL(ws2.Range("A1:A14")) & ")"
strSQL = strSQL & "  )"
strSQL = WorksheetFunction.Trim(strSQL)
Debug.Print strSQL '<-- the moment of truth

Then to use it:

.CommandText = Array(strSQL)

Possibly it will also work directly from your code:

Debug.Print Join(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') AND (PHPICK00.PHPKTN " & MakeSQL(ws2.Range("A1:A14")) & "))"))

These array arguments are kind of screwy but that's how these machines operate when they construct their SQL command texts.

Just my debugging steps - I typically view every SQL statement I write in code this way, and then fix the errors I just made ... I also like to view the statement in a good text editor (my favorite is Notepad++) since you'll get parenthetical matching (and even code highlighting if you like) - more tools to make sure you have it all right.

 

[Norie]

xenou

Bit overkill, a line for almost every field, clause and parenthesis.

Only joking, looks quite good actually but I don't know if I'd have the patience.

By the way, and I'm not 100$ on this, you don't need the Array(...) if you are using a SQL string.

 

[xenou]

By the way, and I'm not 100$ on this, you don't need the Array(...) if you are using a SQL string.

Yeah, I'm not sure either - figured I'd drop it in an array just to be safe.

 

[supdawg]

Thanks both.. We found the issue.

It was fixed by moving the SQL statement to a string.

str = "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.PHWHSE='BNA') AND (PHPICK00.PHPSTF<='90') AND (PHPICK00.PHPKTN " & MakeSQL(Sheet2.Range("A1:A" & LastRow)) & "))"

.CommandText = str

I guess moving it out of the array fixed it?

 

[Norie]

Did I not suggest that in the other thread?

If I didn't I mean to.

The SQL when you record this sort of thing always seems a bit strange, eg field names split up.

I'm going to make changes, even just tidying the code up, I move the SQL out to a separate string.

Doing that also helps when debugging as you can change the SQL a lot easier.

Anyway, it seems to have worked for you and that's the important thing.

 

[supdawg]

Thanks Norie!!

One other issue I've found.

I can run up to 2000 values in my string, but sometime after I do 2000, i get a general ODBC error. It's not a huge deal as I can just loop through 2k at a time if needed, but I am curious about this limitation if anyone knows. I've checked string length and it was under 60k. Not really sure what else I could check to see why it would be failing when using over 2k values in my range.