MS Access - Filter Report by Dates

I have a table Tickets that contains a Date field. The data in these fields are in Date format as well (4/26/2010).

I'm trying to run a report on the Tickets table, but I want to filter by dates. I figured out how to filter today's date, but how would I filter Weekly, Monthly, and Yearly?

I would like my weekly filter to use the previous full Monday-Friday week. Monthly and Yearly are obvious as far as what I'm looking for.

Extra - Also, for yearly, is there any way to set up a combo box that will display the current year as well as the years that previous data contain. Example: if today were 2014 and the first record of the database was entered in 2010, I would want the combo box to show 2010, 2011, 2012, 2013, and 2014 as selections.

Thanks for your help!
kmaris15Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
are you not reading the comments;
<the value displayed in the message box will be the filter that you will use in your report..>

test this
DB.mdb
0
 
Rey Obrero (Capricorn1)Commented:
you wll need a form to do this.
in the form, create  a combobox that you will use for selection, or
an optionframe with three options Weekly, Monthly, and Yearly.

0
 
kmaris15Author Commented:
I have the form created as well as a combo box.

cmdTimeline contains Daily, Weekly, Monthly, and Yearly selections.
cmdTimelineTarget will contain the Months of the year, and years that the data has existed through.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
kmaris15Author Commented:
the cmd's should be cmb's :)
0
 
Rey Obrero (Capricorn1)Commented:
can you upload your db.
0
 
kmaris15Author Commented:
Done.
DB.mdb
0
 
Helen FeddemaCommented:
For a combo box displaying choices of years (counting from the current year), see my Access Archon #146, Filtering by Month and Year.  I use a function (listed below) to create a list of month-year combinations.  You could modify it to just create a list of years.  The function's return value is used as the row source of a combo box.
Here is a link for downloading the article and sample database:
http://www.helenfeddema.com/Files/accarch146.zip
Public Function CreateMonthList() As String
'Created by Helen Feddema 20-Mar-2006
'Modified by Helen Feddema 31-Mar-2006

On Error GoTo ErrorHandler

   Dim strMonthList As String
   Dim strMonth As String
   Dim strMonthMinus1 As String
   Dim strMonthMinus2 As String
   Dim strMonthMinus3 As String
   Dim strYear As String
   Dim strYearMinus1 As String
   Dim strYearMinus2 As String
   Dim strYearMinus3 As String
   Dim dteMonthMinus1 As Date
   Dim dteMonthMinus2 As Date
   Dim dteMonthMinus3 As Date
   

On Error GoTo ErrorHandler

   dteMonthMinus1 = DateAdd("m", -1, Date)
   dteMonthMinus2 = DateAdd("m", -2, Date)
   dteMonthMinus3 = DateAdd("m", -3, Date)
   
   strMonth = Format(Date, "mmmm")
   strMonthMinus1 = Format(dteMonthMinus1, "mmmm")
   strMonthMinus2 = Format(dteMonthMinus2, "mmmm")
   strMonthMinus3 = Format(dteMonthMinus3, "mmmm")
   
   strYear = Year(Date)
   strYearMinus1 = Year(dteMonthMinus1)
   strYearMinus2 = Year(dteMonthMinus2)
   strYearMinus3 = Year(dteMonthMinus3)
   
   strMonthList = strMonthMinus3 & " " & strYearMinus3 & ", " _
      & strMonthMinus2 & " " & strYearMinus2 & ", " _
      & strMonthMinus1 & " " & strYearMinus1 & ", " _
      & strMonth & " " & strYear
      
   CreateMonthList = strMonthList
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0
 
kmaris15Author Commented:
Thank you for your help Helen. Is their possibly an easier way to do this? I can't quite figure out how I would translate that into my database.
0
 
Rey Obrero (Capricorn1)Commented:
see this. select a value on the first combo box
the value displayed in the message box will be the filter that you will use in your report..
DB.mdb
0
 
kmaris15Author Commented:
capricorn, thats very close to what i'm looking for, but how do i get it to show the report of my choice from the timeline i choose?

would i have to implement the startDate and endDate into a SQL string?

I want to be able to select the weekly, monthly, or yearly and have the timeline you programmed in but i need it to filter my reports based on the selection as well.
0
 
kmaris15Author Commented:
I guess this is my problem.

    If TimelineComboBox.Value = "Weekly" And ReportTypeComboBox.Value = "Open Tickets" And_ ReportTargetComboBox.Value = "All Technicians" Then
        DoCmd.OpenReport "Tickets", acViewPreview, , "[Open Date] " &_ getTimeRange(Me.TimelineComboBox)
    End If

How would I also filter that to show only tickets with [Status] = 'Open' within any selected period of time? Thats where I'm running into the trouble.

Thank you for your help.
0
 
kmaris15Author Commented:
There shouldnt be a _ in front of getTimeRange(Me.TimelineComboBox). I added it to show its on the same line but it didn't show correctly after I posted :/
0
 
kmaris15Author Commented:
Update: still can't get it to filter properly. It's filtering the dates selection of my choice just fine, just not any other type of filter I'm trying to do such as "[Status] = 'open'" and such.
0
 
Rey Obrero (Capricorn1)Commented:
kmaris15,
i believe the db i posted already satisfied your requirement about the date filter..
pls post another q for your other requirements
0
 
kmaris15Author Commented:
Good job, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.