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

supdawg

Well-known Member
Joined
Mar 18, 2007
Messages
608
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:

Code:
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:

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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
^^ 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:
Code:
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:
Code:
.CommandText = Array(strSQL)

Possibly it will also work directly from your code:
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.
 
Upvote 0
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.
 
Upvote 0
:)

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.
 
Upvote 0
Thanks both.. We found the issue.

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

Code:
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)) & "))"

Code:
.CommandText = str

I guess moving it out of the array fixed it?
 
Upvote 0
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.:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,834
Members
452,947
Latest member
Gerry_F

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