Need to Pass Array Value to Query

[bconner]

I am trying to pass an Array value to a SQL query in vba. I keep getting the error "Expected Array" Basically I want to pass the string value of strArrayPayor to the SQL code as a string. See my code below.... Arrays are new to me but I am trying to learn....

Private Sub cmdExportToExcel_Click()
Dim strArrayPayor As String
Dim i As Variant
Dim db As Database
Dim SQL As String
 
On Error GoTo err_handler
Set db = CurrentDb
'Grab Username from Windows Environment
User = Environ$("USERNAME")
 
strArrayPayor = Array("AETNA", "BCBS", "CHAMPVA", "CHARITY HOSPITAL", "CIGNA", "COMMERCIAL", "CONTRACTED", "HUMANA", "MEDICAID", "MEDICARE", "METCARE", "MOTOR VEHICAL ACCIDENT", "TRICARE", "UNITED", "WORKERS COMP")
 
For i = LBound(strArrayPayor) To UBound(strArrayPayor)
SQL = "SELECT *"
        SQL = SQL + " FROM Group_" & cmbGroups & "_ATB_Daily_Grouped"
        SQL = SQL + " LEFT JOIN Dictionaries.dbo.Tbl_Fsc_RptCat2_Grouped ON [FSC Reporting Category 2] = [FSC_REPORT_CATEGORY_2]"
        SQL = SQL + "WHERE ([REJECTION_1] IS NULL AND Grouping = '" & strArrayPayor & "')"
 
        db.QueryDefs("Reports").Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MWNSQLP16;Trusted_Connection=YES;Database=ATB;"
        db.QueryDefs("Reports").SQL = SQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Reports", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "UnResponded.xls", True
 
 
Next i
Exit Sub
err_handler:
MsgBox Err.Number & Err.Description

 

[bhamilton42]

Hello,

first off, did you know you can concatenate strings using & _ ? I think that it better programming wise than extending the variable like you do..

your SQL lines
SQL = "SELECT *"
SQL = SQL + " FROM Group_" & cmbGroups & "_ATB_Daily_Grouped"
SQL = SQL + " LEFT JOIN Dictionaries.dbo.Tbl_Fsc_RptCat2_Grouped ON [FSC Reporting Category 2] = [FSC_REPORT_CATEGORY_2]"
SQL = SQL + "WHERE ([REJECTION_1] IS NULL AND Grouping = '" & strArrayPayor & "')"

could be replaced with

SQL = "SELECT *" & _
" FROM Group_" & cmbGroups & "_ATB_Daily_Grouped" & _
" LEFT JOIN Dictionaries.dbo.Tbl_Fsc_RptCat2_Grouped ON [FSC Reporting Category 2] = [FSC_REPORT_CATEGORY_2]" & _
"WHERE ([REJECTION_1] IS NULL AND Grouping = '" & strArrayPayor & "')"


Just a littel tip, use it if you liek or dont if not,

Second, I think I see whats going on with the array. the line "...AND Grouping = '" & strArrayPayor" I think you want to insert the current record as aposed to the whole array right? so the first time the array runs it would be ".....AND Grouping = '" & AETNA" and the next time it would be "....AND Grouping = '" & BCBS" is that correct? so the loop iterates through teh array and each time it puts the current grouping in there yes?

For that, you'd put ".....AND Grouping = '" & strArrayPayor(i)" which says, strArrayPayor at position i. So the first time the loop runs 'i' will be 1, and you'll insert strArrayPayor at position 1, so AETNA, and so on.

Third, arrays in VBA are wierd. your method of populating the array doesnt work, (at least when i tried it gave me an error) so you would do it like this

Dim strArrayPayor As String
Dim arrayPayor() As String
strArrayPayor = "AETNA, BCBS, CHAMPVA , CHARITY HOSPITAL , CIGNA , COMMERCIAL , CONTRACTED , HUMANA , MEDICAID , MEDICARE , METCARE , MOTOR VEHICAL ACCIDENT , TRICARE , UNITED , WORKERS COMP "
arrayPayor = Split(strArrayPayor, ", ")

It seems to me the only way to populate arrays are one at a time either by going array(1) = "" array(2) = "" or with a loop, and otherwise you'd use this split method here. So basically, strArrayPayor is a string, not an array, and all you do it take the quotes out and have 1 at beginning and 1 at end. leave the commas in. then you have a seperate array variable arrayPayor, and you use the split method to populate that. you put strArrayPayor as the input string, and you put "," as the delimeter so it splits your input string by commas. Then in your SQL string youd substitute strArrayPayor with ArrayPayor

So this all should solve tyour problems with that post what happens so I know Good luck!

 

[bconner]

bhamilton42, I was finally able to get it to work by changing i to integer datatype and passing strArrayPayor(i) to the SQL like you suggested. Thanks for your help I appreciate it.... Also thanks for the tip on writing SQL in vba.

Dim strArrayPayor As Variant
Dim i As Integer
Dim db As Database
Dim SQL As String

On Error GoTo err_handler
Set db = CurrentDb
'Grab Username from Windows Environment
User = Environ$("USERNAME")

strArrayPayor = Array("AETNA", "BCBS", "CHAMPVA", "CHARITY HOSPITAL", "CIGNA", "COMMERCIAL", "CONTRACTED", "HUMANA", "MEDICAID", "MEDICARE", "METCARE", "MOTOR VEHICAL ACCIDENT", "TRICARE", "UNITED", "WORKERS COMP")

For i = LBound(strArrayPayor) To UBound(strArrayPayor)
SQL = "SELECT *"
        SQL = SQL + " FROM Group_" & cmbGroups & "_ATB_Daily_Grouped"
        SQL = SQL + " LEFT JOIN Dictionaries.dbo.Tbl_Fsc_RptCat2_Grouped ON [FSC Reporting Category 2] = [FSC_REPORT_CATEGORY_2]"
        SQL = SQL + "WHERE ([REJECTION_1] IS NULL AND Grouping = '" & strArrayPayor(i) & "')"
        
        db.QueryDefs("Reports").Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MWNSQLP16;Trusted_Connection=YES;Database=ATB;"
        db.QueryDefs("Reports").SQL = SQL
'        DoCmd.OpenQuery "Reports"
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Test", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "UnResponded.xls", True
Next i
Exit Sub
err_handler:
MsgBox Err.Number & Err.Description