Results 1 to 8 of 8
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Can't get a filter form for a report working when using a date and combo filter

    I've got a form that I'm trying to use to provide the user the ability to filter down a report. I've got a date field filter (two text boxes) and a combo box for locations. So essentially the user would be able to filter down to a certain date set and/or select a particular location. I have the code inputted into the 'on click' of a button.



    My issue is that when I input only dates, the button doesn't open the report at all. I need to have something selected in the combo box in order for it to open. And then when I do and the report opens, the dates filter correctly but the combo box doesn't filter down to the location.

    Here's my code:

    Code:
    Private Sub btnOpenReport_Click()
    
        Dim strReport As String
        Dim strDateField As String
        Dim strWhere As String
        Dim lngView As Long
        Dim strFilter As String
        Set frm = Forms!frmSearchTool
        strFilter = ""
        Const strcJetDate = "\#mm\/dd\/yyyy\#"
        
        strReport = "rptSearchTool"
        strDateField = "[SubmissionDate]"
        lngView = acViewReport
        
        If IsDate(Me.txtStartDate) Then
            strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
        End If
        If IsDate(Me.txtEndDate) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        End If
        
        If Len("" & frm!ComboLocation) = 0 Then
            Me.Filter = ""
            Me.FilterOn = False
        Exit Sub
        End If
        
        'Location
        If Len("" & frm!ComboLocation) > 0 Then
        strFilter = "[Location] = '" & frm!ComboLocation & "'"
        End If
        
        'Add filter
        Me.Filter = strFilter
        Me.FilterOn = True
        
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
        
        DoCmd.OpenReport strReport, lngView, , strWhere
    
    
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        If Err.Number <> 2501 Then
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
        End If
        Resume Exit_Handler
    End Sub

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you get an error message?
    What is the value of strWhere when you open the report?

    Add a debug.print before the DoCmd.OpenReport to verify the value --adjust as necessary if there is an issue.

    Remove the FilterOn if the combo is not valued.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You should be creating the report WHERE parameter from your search form, not a report event. If you pick a combo value and expect it to work in conjunction with the dates, then you have no AND between the 2nd date and the location (at least not that I can see).
    Perhaps download this and see how it's done from a form. You don't have to have the records in the form itself as per the example but it should show you how to string together your criteria values.

    http://allenbrowne.com/ser-62.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    1. You have error handlers but no On Error GoTo statement. I added this to the top of the sub
    2. the date criteria was being built in a variable called strWhere. The location criteria was being built in strFilter.
    3. strWhere was being used to filter the report but strFilter was only being used to filter the form
    4. if the location combo was blank it was coded to exit the sub early

    See attached changes for you to test.

    Code:
    Private Sub btnOpenReport_Click()
    On Error GoTo Err_Handler
        Dim strReport As String
        Dim strDateField As String
        Dim strWhere As String
        Dim lngView As Long
        Dim strFilter As String
        Set frm = Forms!frmSearchTool
        strFilter = ""
        Const strcJetDate = "\#mm\/dd\/yyyy\#"
        
        strReport = "rptSearchTool"
        strDateField = "[SubmissionDate]"
        lngView = acViewReport
        
        If IsDate(Me.txtStartDate) Then
            strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
        End If
        If IsDate(Me.txtEndDate) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        End If
        
        'If Len("" & frm!ComboLocation) = 0 Then
        '    Me.Filter = ""
        '    Me.FilterOn = False
        '    Exit Sub
        'End If
        
        'Location
        'If Len("" & frm!ComboLocation) > 0 Then
        If Not IsNull(frm!ComboLocation) Then
            'strFilter = "[Location] = '" & frm!ComboLocation & "'"
            If strWhere <> "" Then
             strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "[Location] ='" & frm!ComboLocation & "'"
        End If
        
        'Add filter
        'Me.Filter = strFilter
        'Me.FilterOn = True
        
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
        
        DoCmd.OpenReport strReport, lngView, , strWhere
    
    
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        If Err.Number <> 2501 Then
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
        End If
        Resume Exit_Handler
    End Sub
    [edit]
    What micron said. I didn't realize this button was on the report and not on a form... I thought there was some funny business going on using both the frm variable and Me.

    for the record I don't see where frm was declared. Make sure you have
    Code:
    Option Explicit
    at the top of every code file

  5. #5
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by kd2017 View Post
    1. You have error handlers but no On Error GoTo statement. I added this to the top of the sub
    2. the date criteria was being built in a variable called strWhere. The location criteria was being built in strFilter.
    3. strWhere was being used to filter the report but strFilter was only being used to filter the form
    4. if the location combo was blank it was coded to exit the sub early

    See attached changes for you to test.

    Code:
    Private Sub btnOpenReport_Click()
    On Error GoTo Err_Handler
        Dim strReport As String
        Dim strDateField As String
        Dim strWhere As String
        Dim lngView As Long
        Dim strFilter As String
        Set frm = Forms!frmSearchTool
        strFilter = ""
        Const strcJetDate = "\#mm\/dd\/yyyy\#"
        
        strReport = "rptSearchTool"
        strDateField = "[SubmissionDate]"
        lngView = acViewReport
        
        If IsDate(Me.txtStartDate) Then
            strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
        End If
        If IsDate(Me.txtEndDate) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        End If
        
        'If Len("" & frm!ComboLocation) = 0 Then
        '    Me.Filter = ""
        '    Me.FilterOn = False
        '    Exit Sub
        'End If
        
        'Location
        'If Len("" & frm!ComboLocation) > 0 Then
        If Not IsNull(frm!ComboLocation) Then
            'strFilter = "[Location] = '" & frm!ComboLocation & "'"
            If strWhere <> "" Then
             strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "[Location] ='" & frm!ComboLocation & "'"
        End If
        
        'Add filter
        'Me.Filter = strFilter
        'Me.FilterOn = True
        
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
        
        DoCmd.OpenReport strReport, lngView, , strWhere
    
    
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        If Err.Number <> 2501 Then
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
        End If
        Resume Exit_Handler
    End Sub
    [edit]
    What micron said. I didn't realize this button was on the report and not on a form... I thought there was some funny business going on using both the frm variable and Me.

    for the record I don't see where frm was declared. Make sure you have
    Code:
    Option Explicit
    at the top of every code file

    This worked!! Thank you!!

  6. #6
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Actually the only remaining issue after testing is that the date filter is not including entries that are the same date as the start date. Meaning if I set the dates as 12/27/2021 to 12/29/2021, I'm only seeing entries from 12/28 and 12/29. Any idea why?

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Maybe try to wrap the Start Date in DateValue():
    Code:
    If IsDate(Me.txtStartDate) Then
            strWhere = "(" & strDateField & " >= " & Format(DateValue(Me.txtStartDate), strcJetDate) & ")"
        End If
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Thank you! That worked

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 04-29-2015, 11:57 PM
  2. Replies: 15
    Last Post: 07-14-2014, 11:04 AM
  3. Filter Report by Optional Date Range and Combo Box
    By ARickert in forum SQL Server
    Replies: 3
    Last Post: 10-17-2012, 10:46 AM
  4. Filter form records by Date with Combo box
    By jhrBanker in forum Forms
    Replies: 2
    Last Post: 07-31-2012, 01:19 PM
  5. Replies: 2
    Last Post: 08-18-2011, 10:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums