472,982 Members | 1,890 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,982 software developers and data experts.

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 "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
  4.    For intCounter = 1 To 7
  5.       If Me("Filter" & intCounter) <> "" Then
  7.          strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And  "
  8.       End If
  9.            Next
  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
0 6658

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

Similar topics

by: arthur-e | last post by:
I can filter a query for a report on a field of a subform BUT now.... I'd like to be able to select more than one item in a multi-list box to select all the records. ALSO to use two fields (or...
by: Sara | last post by:
I have followed instructions on the http://allenbrowne.com/tips.html for limiting a report to a date range. At the bottom there is a note that says You will end up using this form for all sorts...
by: Tsair | last post by:
Select PoHead.*, PoItem.*, PoItem.Tranno as Tranno from PoItem INNER JOIN ON PoItem.Tranno = PoHead.Tranno Where PoHead.TranDate > #5/10/2005# AND PoHead.TranDate < #9/10/2005# AND TRUE ORDER BY...
by: Eddie901 | last post by:
I have a date field in a table formatted as a "Medium Date", e.g., 29-Mar-06. When I filter by that selection it's okay when the date is after the 13th of the month, but for the 12th or below it...
by: Cara Murphy | last post by:
Hi There! Hoping you are able to help me with a filtering scenario: I have created a pop up form (Filter) to filter a report (Open Issues), which currently has a number of combo boxes which...
by: jambonjamasb | last post by:
I am wanting to create a report that summarises the number of items within a date range. For example I have a FIELD called System_Change. This is a drop down COMBOBOX that uses words like unix,...
by: mintz87 | last post by:
i have a table that has a beg_date & end_date (3/1/1985 & 3/1/1990) how do i, in access, break out this date range into another table by month. since this date range is a 60 month range it would...
by: ken | last post by:
I'm trying to implement Allen Browne's advice from http://allenbrowne.com/casu-08.html. Unfortunately, I'm getting this error: Run-time error '438': Object doesn't support this property or...
by: Lisa B | last post by:
I have managed to create a Report from a form that displays data per date In the query criteria for the date it references the form !! The report is based on the results of the query as per...
by: epifinygirl | last post by:
I currently have a report that summarizes my data for each client from the table (each page summarizes the information for each client). I have VBA code all set that can filter each client and print...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.