MS Access Filter Report by Selection (Add Date Range Fields)

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • LBinGA
    New Member
    • Feb 2010
    • 14

    MS Access Filter Report by Selection (Add Date Range Fields)

    Hello! I am using Access 2010 and I have an Unbound Form (FrmFilter) that filters a report by (up to) 7 criteria using the code below, which works beautifully. I would like to add 2 additional unbound date fields to filter by as well to see only the specified date range in the report:
    Start & End Date, both filtering the field "dteAuditDa te".
    These would be Filter8 & Filter9.

    I've tried adding to the following code but I'm doing something wrong because the date filter overrides the other 7 criteria every time I click Run Report or it doesn't work at all.

    Can someone help me update my code to add two fields to get a Date Range as well as any of the 1-7 of the selections made?

    Code below is for Filters 1-7 only. I would like to add 8 & 9 as Start & End Date for the "dteAuditDa te" field.

    Code:
    Private Sub Set_Filter_Click()
        Dim strSQL As String, intCounter As Integer
    
       For intCounter = 1 To 7
          If Me("Filter" & intCounter) <> "" Then
    
             strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And  "
          End If
               Next
    
            If strSQL <> "" Then
            ' Strip Last " And ".
              strSQL = Left(strSQL, (Len(strSQL) - 5))
              Reports![rptData].Filter = strSQL
              Reports![rptData].FilterOn = True
            End If
    End Sub
    I am able to create a form that filter solely on date ranges but I need the function integrated into the unbound form I already have. However, I'm not opposed to possible solutions other than adding to this code as long as it will filter the report both on Date Range provided & the other 7 available selections. Thanks so much in advance.

    LB in GA
Working...