By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,406 Members | 1,353 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,406 IT Pros & Developers. It's quick & easy.

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

P: 14
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 "dteAuditDate".
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 "dteAuditDate" field.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Set_Filter_Click()
  2.     Dim strSQL As String, intCounter As Integer
  3.  
  4.    For intCounter = 1 To 7
  5.       If Me("Filter" & intCounter) <> "" Then
  6.  
  7.          strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And  "
  8.       End If
  9.            Next
  10.  
  11.         If strSQL <> "" Then
  12.         ' Strip Last " And ".
  13.           strSQL = Left(strSQL, (Len(strSQL) - 5))
  14.           Reports![rptData].Filter = strSQL
  15.           Reports![rptData].FilterOn = True
  16.         End If
  17. 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
Oct 24 '13 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.