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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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.
kmaris15Author Commented:
the cmd's should be cmb's :)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
can you upload your db.
kmaris15Author Commented:
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:
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
   Exit Function

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

End Function

Open in new window

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.
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..
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.
Rey Obrero (Capricorn1)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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 :/
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.
Rey Obrero (Capricorn1)Commented:
i believe the db i posted already satisfied your requirement about the date filter..
pls post another q for your other requirements
kmaris15Author Commented:
Good job, thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.