Passing SQL String to Pass Thru Query

[bconner]

I am passing a SQL string to a Pass Thru query (SQL Server Backend). When the User selects a report from [cmbReports] they will also have the Option to select a payor from [cmbFscRptCat2Grouping]. Basically I would like for the user to be able to run the report with or without a selection from combo box [cmbFscRptCat2Grouping].

The below code works as long as a selection is made in [cmbFscRptCat2Grouping]

Select Case cmbReports
 Case "Unresponded"
      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 = '" & cmbFscRptCat2Grouping & "')"
      
      db.QueryDefs("Reports").Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MWNSQLD16;Trusted_Connection=YES;Database=ATB;"
      db.QueryDefs("Reports").SQL = SQL
      DoCmd.OpenQuery "Reports"

 

[pbaldy]

Stop the string before that, test the combo and add the " AND..." bit if a selection was made.

 

[mdlueck]

I did some mussing on developing a query based on a form with multiple search criteria here...

Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs
http://www.access-programmers.co.uk/forums/showthread.php?t=230838

 

[bconner]

I added an If statement inside the Case statement to test for value and it worked like a charm see below

Case "Unresponded"
      If IsNull(cmbFscRptCat2Grouping) = False Then
        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 = '" & cmbFscRptCat2Grouping & "')"
 
        db.QueryDefs("Reports").Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MWNSQLD16;Trusted_Connection=YES;Database=ATB;"
        db.QueryDefs("Reports").SQL = SQL
        DoCmd.OpenQuery "Reports"
      Else
        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"
 
        db.QueryDefs("Reports").Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MWNSQLD16;Trusted_Connection=YES;Database=ATB;"
        db.QueryDefs("Reports").SQL = SQL
        DoCmd.OpenQuery "Reports"
      End If

Thanks pbaldy for the suggestion

 

[pbaldy]

That's the brute force method. I meant this:

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"

If IsNull(cmbFscRptCat2Grouping) = False Then
  SQL  = SQL & " AND Grouping = '" & cmbFscRptCat2Grouping & "'"
End If

db.QueryDefs("Reports").Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MWNSQLD16;Trusted_Connection=YES;Database=ATB;"
db.QueryDefs("Reports").SQL = SQL
DoCmd.OpenQuery "Reports"

 

[bconner]

Thanks mdlueck and pbaldy your suggestions were very helpful...