VBA SQL multiple parameter error

[mhussma1]

I used macro recorder to create two SQL queries for me. The first query works fine and I can put in variables in the Where clause, but the second only works when my string is less than 5 parameters. I can't figure out why 1, 2, 3 and 4 parameters work but anything more than that doesn't. Ultimately I'd like to have 150 parameters in the Where clause.

If TOTAL_MRNS <= 4 then this works.
If TOTAL_MRN > 4 then I get a run time error 13 at the .CommadText = Array line.

Any thoughts why 4 lines will work but not 4+?

Dim INPUT_BEGIN As String
Dim INPUT_END As String
Dim INPUT_PROC As String
Dim INPUT_BEGIN2 As String
Dim INPUT_END2 As String
Dim Amb_Surg As Integer
Dim Outpatient As Integer
Dim Inpatient As Integer
Dim INPUT_TYPE As String
Dim MRN_Range As String
Dim MRN_Range_Query As String
Dim SSQL As String


INPUT_BEGIN = InputBox("Enter the Begining Date")
INPUT_END = InputBox("Enter the End Date")
INPUT_PROC = InputBox("Enter the CPT code")

INPUT_BEGIN2 = Format(INPUT_BEGIN, "YYYY-MM-DD")
INPUT_END2 = Format(INPUT_END, "YYYY-MM-DD")


With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=HFHS_CDS;UID=mhussma1;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=OFP630871;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT PATIENT_CHARGES.MRN, PATIENT_CHARGES.SERV_DT, PATIENT_CHARGES.PAT_TP_CD, PATIENT_CHARGES.PROC_CD, PATIENT_CHARGES.INS_CD_PRI" & Chr(13) & "" & Chr(10) & "FROM CDS.EN01.PATIENT_CHARGES PATIENT_CHARGES" & Chr(13) & "" & Chr(10) & "WHERE (PATIENT_CHARG" _
, _
"ES.SERV_DT>={ts '" & INPUT_BEGIN2 & " 00:00:00'} And PATIENT_CHARGES.SERV_DT<={ts '" & INPUT_END2 & " 00:00:00'}) AND (PATIENT_CHARGES.PROC_CD='" & INPUT_PROC & "')" & Chr(13) & "" & Chr(10) & "ORDER BY PATIENT_CHARGES.MRN" _
)
.Name = "Query from HFHS_CDS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Range("A1").End(xlDown).Select
PROC_lastrow = ActiveCell.Row

Amb_Surg = WorksheetFunction.CountIf(Range("C2", Cells(PROC_lastrow, 3)), "A")
Outpatient = WorksheetFunction.CountIf(Range("C2", Cells(PROC_lastrow, 3)), "O")
Inpatient = WorksheetFunction.CountIf(Range("C2", Cells(PROC_lastrow, 3)), "I")

INPUT_TYPE = InputBox("Amb Surg = " & Amb_Surg & " " & vbCrLf & "Outpatient = " & Outpatient & " " & vbCrLf & "Inpatient = " & Inpatient & " " & vbCrLf & "Please select A, O or I")


Sheets.Add
Sheets("Sheet1").Select
Range("A1:E1").AutoFilter
Range("A1:E1").AutoFilter Field:=3, Criteria1:=INPUT_TYPE
Range("A2", ActiveCell.SpecialCells(xlLastCell)).Copy
Sheets("Sheet4").Select
Range("A1").PasteSpecial xlPasteValues
Range("A1").End(xlDown).Select
MRN_lastrow = ActiveCell.Row

If MRN_lastrow > 150 Then
MRN_firstrow = MRN_lastrow - 150
Else
MRN_firstrow = 1
End If
TOTAL_MRNS = MRN_lastrow - MRN_firstrow

MRN_Range = CStr("PATIENT_CHARGES.MRN = '" & Cells(MRN_firstrow, 1).Value & "' OR ")

For i = 1 To TOTAL_MRNS
MRN_Range = MRN_Range & CStr("PATIENT_CHARGES.MRN = '" & Cells(MRN_firstrow, 1).Offset(i, 0).Value & "' OR ")
Next i
MRN_Range_Query = Left(MRN_Range, Len(MRN_Range) - 4)

Sheets("Sheet2").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=HFHS_CDS;UID=mhussma1;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=OFP630871;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT PATIENT_CHARGES.SERV_DT, PATIENT_CHARGES.MRN, PATIENT_CHARGES.MRN_SFX_CD, PATIENT_CHARGES.PROC_CD, PATIENT_CHARGES.PROC_MOD, PATIENT_CHARGES.SERV_CD, PATIENT_CHARGES.CHG_AMT, PATIENT_CHARGES.IN" _
, _
"S_CD_PRI, PATIENT_CHARGES.FIN_CLS_CD, PATIENT_CHARGES.PAT_TP_CD" & Chr(13) & "" & Chr(10) & "FROM CDS.EN01.PATIENT_CHARGES PATIENT_CHARGES" & Chr(13) & "" & Chr(10) & "WHERE (PATIENT_CHARGES.SERV_DT>={ts '" & INPUT_BEGIN2 & " 00:00:00'} And PATIENT_CHARGES.SERV_DT<=" _
, _
"{ts '" & INPUT_END2 & " 00:00:00'}) AND (" & MRN_Range_Query & ")" & Chr(13) & "" & Chr(10) & "ORDER BY PATIENT_CHARGES.MRN, PATIENT_CHARGES.SERV_DT" _
)
.Name = "Query from HFHS_CDS_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[RoryA]

Each part of the Array can only be 255 characters. You can simply skip the array and pass one long string, but if you are trying to uise 150 parameters in one SQL statement, you really need to rethink what you are doing IMO. It looks to me as though what you want is an IN clause rather than multiple WHERE clauses on the same field.

 

[mhussma1]

I tried using Where IN then setting the string 'XXXXXXXX', 'XXXXXXXX', 'XXXXXXXX', etc but I got a type mismatch. I would prefer to use Where IN but that didn't work either. Any thoughts on that?

 

[RoryA]

That should work (but again subject to the 255 character limitation I mentioned) assuming the field is a text field of some sort.

 

[mhussma1]

Is there a way to get around the 255 character limit? Currently mine errors at 70, but I would like to have a string with around 5,000 characters to query all at once.

 

[RoryA]

Don't use the array function, as I said - just pass one string to the CommandText. Sounds inefficient though - can you not load your criteria range to a table in the database and then extract based on that?

 

[mhussma1]

I put the command line as a string (deleted the array) and it worked! Thank you so much!! You don't realize how much you just helped me!