Date/Time Filters By Year

[Luke]

I have a table with a field called "ReviewDate" with a Date/Time format. I also have a field in this table called "ReviewNote". This allows me to write multiple Review Notes/Memos for all of the companies as well as the date on which I wrote it (ReviewDate). Now I have created a Report in which I can view all of the Review Notes and the date on which each Note was written. The problem is my boss wants to be able to view the Review Notes for particular years.

I have viewed the Report in Layout View and used the "Date Filter" Option to enter "Between Oldest: 1/1/2011 and Newest: 12/31/2011" in order to view only those notes that were written in 2011. This works, but when I save, close, and re-open the report, the date filter is no longer there and it just shows all of the years again. I don't know why it isn't working.

I was also looking into query criteria and trying to use some sort of expression to show only particular years but I'm having trouble figuring out which expression or function to use. Does anybody know how I can solve this problem so that I can create a Report that shows ReviewNotes and dates for a particular year? I was also thinking of using a Paramter Value Box for the ReviewDate criteria that asks you to [Enter the Year]. Thanks a lot.



-Luke

[June7]

Nearly impossible to validate input of parameter prompts. Construct a YearReview field in the query with an expression like Year(ReviewDate). Design a form where use enters year then reference this criteria to filter the report and code in an event (button click or AfterUpdate) would open the report with DoCmd.OpenReport method.

Then:

1. Refer to the control on open form in the query the report is based on. Criteria under the YearReview could refer to the form control.

or

2. Use the WHERE clause of DoCmd.OpenReport to filter the report by referencing the form. DoCmd.OpenReport "reportname", , , "YearReview=" & Me.txtYear