Filtering Reports...

Reports are a way to organize and present data to Users from the simple to the more complex.  Here we'll review a couple of different way to filter reports giving Users control over what they see.  Typically you have...

If your field is numeric...
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

If your field is text…
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]='" & Me![ControlOnForm] & "'"

But we want to give them more flexibility...
The difficult I do immediately, the impossible takes a little bit longer.
Click to send feedback...
Tips (Main)
Print Full or Partial Sheet of Same Labels
Print One or More Labels for One or More Clients (Records)
Multiple copies of 1(one) Report using a Cartesian Product query
Keep Main Report/Subreport Records Together
Multicolumn Report with Images
Ranking Reports Using Shapes
Print Constant Number of Lines (or add a Constant Number of Lines)
Custom Page Header
Single Report Filter
Multiple Reports Filter
Once you decide what fields you want to filter on you can create your Form.  When making your Form be sure to set it as a pop-up and moveable.  Afterall, you want them to be able to move it out of the was of the Report.  In the example above it's not especially important as the Report is going out to Excel but if using with an Access Report then the Form is guaranteed to stay on top.  This way the User can change the Filters without opening and closing the Report or the Form.  Once your Form is done you can start adding the code, see below...

(Note, if using an Excel report then you are going to have to close the open Excel workbook before outputting the updated one or your User will get an error message; the Access Report will simply refresh so no worries there.)
Private Sub cmdSendToExcel_Click()
        Dim dbs As DAO.Database
        Dim qryDef As DAO.QueryDef
        Dim strSQL As String
        Dim strWhere As String
        Dim lngLen As Long
        Set dbs = CurrentDb
    strSQL = "SELECT blah, blah, blah...
    If Not IsNull(Me.cboCustomerID) Then
        strWhere = strWhere & "([crCustomerID] = " & Me.cboCustomerID & ") AND "
    End If
    If Not IsNull(Me.txtRunDate) Then
        strWhere = strWhere & "([crRunDate] >= " & Format(Me.txtRunDate, conJetDate) & ") AND "
    End If
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([crRunDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
    If Not IsNull(Me.txtEndDate) Then
        strWhere = strWhere & "([crRunDate] <= " & Format(Me.txtEndDate, conJetDate) & ") AND "
    End If
    If Not IsNull(Me.cboStatusID) Then
        strWhere = strWhere & "([crStatusID] = """ & Me.cboStatusID & """) AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        strSQL = strSQL
        Set qryDef = dbs.CreateQueryDef("qryExportCoilRunCheck", strSQL)
        'DoCmd.OpenQuery qryDef.Name
        Set qryDef = Nothing
        Call SendToExcel("qryExportCoilRunCheck", "RunCheck")
        DoCmd.DeleteObject acQuery, "qryExportCoilRunCheck"
        strWhere = Left$(strWhere, lngLen)
        strSQL = strSQL & " WHERE " & strWhere
        Set qryDef = dbs.CreateQueryDef("qryExportCoilRunCheck", strSQL)
        'DoCmd.OpenQuery qryDef.Name
        Set qryDef = Nothing
        Call SendToExcel("qryExportCoilRunCheck", "RunCheck")
        DoCmd.DeleteObject acQuery, "qryExportCoilRunCheck"
    End If
        Set dbs = Nothing
End Sub
Function SendToExcel(strTQName As String, strSheetName As String)
'strTQName is the name of the table or query you want to send to Excel
'strSheetName is the name of the sheet you want to send it to
        Dim rst As DAO.Recordset
        Dim ApXL As Object
        Dim xlWBk As Object
        Dim xlWSh As Object
        Dim strPath As String
        On Error GoTo Err_Handler
            'strPath = "C:\ctl\ReportTemplates\rptCoilRunCheck.xlsx"
            Set rst = CurrentDb.OpenRecordset(strTQName)
            Set ApXL = CreateObject("Excel.Application")
            Set xlWBk = ApXL.Workbooks.Open(strPath)
            ApXL.Visible = True
            Set xlWSh = xlWBk.Worksheets(strSheetName)
            xlWSh.Range("A3").Value = Date
            xlWSh.Range("A5").CopyFromRecordset rst
            ' selects the first cell to unselect all cells
        Set rst = Nothing
        'Remove prompts to save the report
        ApXL.DisplayAlerts = False
        xlWBk.SaveAs "C:\ctl\MyReports\CoilRunCheck.xlsx", 51
        ApXL.DisplayAlerts = True
        Exit Function
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        Exit Function
End Function
Module behind the Form
Code on Command Button
The Mulitple Report Filter utilizes the multiple types of filters including the code shown above only it's within a Select Case statement, i.e.
Select Case Me.cboReportOpener
    Case 1  'Report 1
	'Filter Code Here
    Case 2  'Report 2
	'Filter Code Here
    Case 3  'Report 3
	'Filter Code Here
End Select
So, you could have a one liner on Case 1, code that send your Report to Excel, like the above on Case 2, and one to PDF on Case 3.

In this example you will need a Table, i.e. tlkpReports, to hold the name of the Reports which will be used as the Row Source for cboReportOpener,  the Select Reports combo box. The Primary Key from that Table is what you will be using in the Select Case statement.

The advantage of this type of Form is if you don't have a lot of Reports or your database has several sections, i.e. Contacts, Invoicing, you can organize them according to section or just have them all in one place.
Private Sub cmdClear_Click()
On Error Resume Next
    'Purpose:   Clear all the search boxes.
    Dim ctl As Control
    'Clear all the controls in the Form Detail section.
    For Each ctl In Me.Section("Detail").Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    ClearListBox Me.lstLocationID  'Only use if you have a List Box
End Sub
I use Command Buttons instead of regular labels on the Multiple Reports Filter so the User doesn't have to clear all the Filters and start again.  They can just clear one or more as they see fit.
Clearing the Filters...
Sub ClearListBox(pctlListBox As ListBox)
     ' Purpose: clear all selection from a list box control
     ' Programmer: Duane Hookom
     ' Called From: Multiple
     ' Date: 2/21/2003
     ' Parameters: list box object
     On Error GoTo ClearListBox_Err
     Dim strErrMsg As String 'For Error Handling
     Dim varitem As Variant
     For Each varitem In pctlListBox.ItemsSelected
     pctlListBox.Selected(varitem) = False
     On Error Resume Next
     Exit Sub
     Select Case Err
     Case Else
     strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf
     strErrMsg = strErrMsg & "Error Description: " & Err.Description
     MsgBox strErrMsg, vbInformation, "ClearListBox"
     Resume ClearListBox_Exit
     End Select
 End Sub
If you use a List Box then you will need to use Duane Hookom's code below to clear it.  You will also need to use it on the Event Procedure of the Command Button to clear the List Box.
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.