Use Query Definitions to Create Dynamic Queries and Reports

The Begin and End dates in this query come from text boxes formatted as dates. The client list comes from a multi-select list box. The "qClaims" query is associated with the Claims Report. The query definition is fed a dynamic SQL statement built from the values selected by the user. The query definition makes it easy to generate a report from a dynamic query.

  

 If Nz(Me.txtBegDt.Value, "") = "" Then

        MsgBox "Please select a begin date."

        Exit Sub

    End If

    If Nz(Me.txtEndDt.Value, "") = "" Then

        MsgBox "Please select an end date."

        Exit Sub

    End If

    

    DoCmd.Close acQuery, "qClaims", acSaveNo

    DoCmd.Close acReport, "Claims Report"

    

    Dim strList as String
    Dim strsql As String

    Dim qdf As DAO.QueryDef

    Set db = CurrentDb

    

    Set qdf = db.QueryDefs("qClaims")

    

    strList = ""

    

    If Me.lstClients.ItemsSelected.Count > 0 Then

        For Each client In Me.lstClients.ItemsSelected

            strList = strList & "'" & Me.lstClients.ItemData(client) & "',"

        Next client

        strList = CStr(Trim(Left(strList, Len(strList) - 1)))   ' Remove trailing comma

        strList = " AND i.client_id IN (" & strList & ")"

    Else

        strList = ""

    End If

    strsql = "SELECT i.client_id, i.client_name, i.fname, i.lname, i.ssn," _
    & " i.sep_reason, c.charge_qu, c.charge_yr, c.charge_amount" _

    & " FROM tCharges c RIGHT JOIN tSeparation_Info i ON c.sep_ID = i.id" _

    & " WHERE i.mail_date BETWEEN #" & [Forms]![Claims Report]![txtBegDt] _

    & "# And #" & [Forms]![Claims Report]![txtEndDt] & "# " & strList _

    & " ORDER BY i.client_name"

            

    qdf.sql = strsql   ' Set the sql of the query definition

    

    DoCmd.OpenReport "Claims Report", acViewReport

    

    Set db = Nothing

    Set qdf = Nothing