Dynamic SQL statements

Dynamic SQL statements are great when you want to give end users maximum flexibility in creating their own  reports. To build dynamic SQL statements, first declare variables for each clause of a SQL statement. Here we have variables for the SELECT, FROM, WHERE, and GROUP BY clauses. The allSQL variable will hold the completed SQL statement.

  Dim selSQL As String 
  Dim fromSQL As String
  Dim whereSQL As String
  Dim groupSQL As String
  Dim allSQL As String
  Dim func As String

Next, we create a query definition object that will be used to run the dynamic SQL statement. "CustomReport" is a query I created in the visual query builder. It's just a container that will ultimately be used to run the dynamic SQL statement.

 Dim qdf As DAO.QueryDef
 Dim db As DAO.Database
 Set db = CurrentDb
 Set qdf = db.QueryDefs("CustomReport")

Next, initialize the SQL query strings.

selSQL = "SELECT m.PayYear"
fromSQL = " FROM client_list c INNER JOIN metrics m ON c.apexID = m.apexID"
groupSQL = " GROUP BY m.PayYear"
whereSQL = " WHERE 1=1"

Next, we will build the SELECT and GROUP BY portions of the SQL statement from values selected on a form:

In this case, we will Sum the Admin Fees and Gross Pay amounts and group those amounts by Salesforce Client, Quarter, and Salesforce Industry. We will be creating our report from a mini data warehouse we've built from data exported out of Salesforce.com and from another system called Summit Apex.

The following statements iterate through the different Display Value fields to retrieve the selected values and apply them to the SQL sections. Notice how the aggregate function is set in a variable called "func." If the user chooses "none" from the Aggregate Function radio group, then we will simply set the groupSQL string equal to "" near the end of the subprocedure.

    ' Time Detail Level
    If Me.optTime.Value = 1 Then
        selSQL = selSQL & ", m.PayMonth"
        groupSQL = groupSQL & ", m.PayMonth"
    ElseIf Me.optTime.Value = 2 Then
        selSQL = selSQL & ", m.quarter"
        groupSQL = groupSQL & ", m.quarter"
    End If
    
    ' Client Level
    If Me.optClLevel.Value = 1 Then
        selSQL = selSQL & ", c.ApexID, c.ApexName"
        groupSQL = groupSQL & ", c.ApexID, c.ApexName"
    ElseIf Me.optClLevel.Value = 2 Then
        selSQL = selSQL & ", c.ApexSFIDCaseSens, c.SalesforceName"
        groupSQL = groupSQL & ", c.ApexSFIDCaseSens, c.SalesforceName"
    End If
    
    ' Aggregate Function
    If Me.optFunc.Value = 1 Then
        func = "SUM"
    ElseIf Me.optFunc.Value = 2 Then
        func = "AVG"
    ElseIf Me.optFunc.Value = 3 Then
        func = "MAX"
    ElseIf Me.optFunc.Value = 4 Then
        func = "MIN"
    ElseIf Me.optFunc.Value = 5 Then
        func = ""
    End If
    
    ' Metrics
    For Each indx In Me.lstMetrics.ItemsSelected
            If func = "" Then
                selSQL = selSQL & ", m." & Me.lstMetrics.ItemData(indx)
            Else
                If func <> "SUM" Then
                    selSQL = selSQL & ", " & func & "(m." & Me.lstMetrics.ItemData(indx) & ") as " & Me.lstMetrics.ItemData(indx) & func & "PerMonth"
                Else
                    selSQL = selSQL & ", " & func & "(m." & Me.lstMetrics.ItemData(indx) & ") as " & Me.lstMetrics.ItemData(indx) & "_" & func
                End If
            End If

    Next indx
    
    ' Attributes
    For Each indx In Me.lstAttr.ItemsSelected
        selSQL = selSQL & ", c." & Me.lstAttr.ItemData(indx)
        groupSQL = groupSQL & ", c." & Me.lstAttr.ItemData(indx)
    Next indx 

Next, we choose from a set of Attribute Filters to start building the WHERE clause.

In this case, we are limiting the results to the year 2013 and to the state of Utah. Here's the code to handle all of the Attribute filters:

   ' Date Range
    If Nz(Me.txtBegDt.Value, "") <> "" And Nz(Me.txtEndDt.Value, "") <> "" Then
        whereSQL = whereSQL & " AND m.MonthEndDate BETWEEN #" & Me.txtBegDt.Value & "# AND #" & Me.txtEndDt.Value & "#"
    End If
    
    'Company Number
    If Nz(Me.txtCoNum.Value, "") <> "" Then
          whereSQL = whereSQL & " AND c.ApexCompany = '" & Me.txtCoNum.Value & "'"
    End If
    
    ' Industry Filter In
    Dim industryList As String
    industryList = ""
    For Each indx In Me.lstIndustry.ItemsSelected
        industryList = industryList & "'" & Me.lstIndustry.ItemData(indx) & "',"
    Next indx
    If industryList <> "" Then
        whereSQL = whereSQL & " AND c.SFIndustry IN (" & Mid(industryList, 1, Len(industryList) - 1) & ")"
    End If
    
    ' State Filter In
    Dim stateList As String
    stateList = ""
    For Each indx In Me.lstState.ItemsSelected
        stateList = stateList & "'" & Me.lstState.ItemData(indx) & "',"
    Next indx
    If stateList <> "" Then
        whereSQL = whereSQL & " AND c.State IN (" & Mid(stateList, 1, Len(stateList) - 1) & ")"
    End If
    
    ' City Filter In
    Dim cityList As String
    cityList = ""
    For Each indx In Me.lstCity.ItemsSelected
        cityList = cityList & "'" & Me.lstCity.ItemData(indx) & "',"
    Next indx
    If cityList <> "" Then
        whereSQL = whereSQL & " AND c.city IN (" & Mid(cityList, 1, Len(cityList) - 1) & ")"
    End If
    
    ' Zip Filter In
    Dim zipList As String
    zipList = ""
    For Each indx In Me.lstZip.ItemsSelected
        zipList = zipList & "'" & Me.lstZip.ItemData(indx) & "',"
    Next indx
    If zipList <> "" Then
        whereSQL = whereSQL & " AND c.zip IN (" & Mid(zipList, 1, Len(zipList) - 1) & ")"
    End If
    
    ' Apex Client Filter In
    Dim apexClList As String
    apexClList = ""
    For Each indx In Me.lstApexClients.ItemsSelected
        apexClList = apexClList & "'" & Me.lstApexClients.ItemData(indx) & "',"
    Next indx
    If apexClList <> "" Then
        whereSQL = whereSQL & " AND c.ApexID IN (" & Mid(apexClList, 1, Len(apexClList) - 1) & ")"
    End If
    
    ' Salesforce Client Filter In
    Dim sfClList As String
    sfClList = ""
    For Each indx In Me.lstSFClients.ItemsSelected
        sfClList = sfClList & "'" & Me.lstSFClients.ItemData(indx) & "',"
    Next indx
    If sfClList <> "" Then
        whereSQL = whereSQL & " AND c.ApexSFIDCaseSens IN (" & Mid(sfClList, 1, Len(sfClList) - 1) & ")"
    End If

By breaking the SQL statement down into its component parts, we can easily add additional filters and attributes to the form to give the end user maximum flexibility in building their own custom reports.

The following code concatenates each SQL section together to create the final statement:

    allSQL = selSQL & fromSQL & whereSQL & groupSQL

I used debug.print to print the allSQL variable to the Immediate window in the VBA editor. Here's what the final SQL statement looks like:

SELECT m.PayYear, m.quarter, c.ApexSFIDCaseSens, c.SalesforceName, SUM(m.AdminFees) as AdminFees_SUM, SUM(m.GrossPay) as GrossPay_SUM, c.SFIndustry
FROM client_list c INNER JOIN metrics m ON c.apexID = m.apexID
WHERE 1=1 AND m.MonthEndDate BETWEEN #1/1/2013# AND #12/31/2013# AND c.State IN ('UT') GROUP BY m.PayYear, m.quarter, c.ApexSFIDCaseSens, c.SalesforceName, c.SFIndustry

In order to run the SQL statement and show the report, we feed the SQL statement to the Query Definition object:

    qdf.SQL = allSQL

Finally, we open the report and display the output:

DoCmd.OpenQuery "CustomReport", acViewNormal

We could just as easily export the report to Excel as follows:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CustomReport", curPath & "\Excel Exports\CustomReport.xlsx"