Filter a report by a Year Range using a form

[Chapster5]

Hi all ,

New here struggling to get a filter to work with Access 2003. Here's what I have

A Query (Called QRY_COST_BY_PERIOD containing data (including a column for year(called YEAR))
A Report with a bar chart in laid out how I need it (Called TRANSPORT_COST_BY_PERIOD_TEMP_REJECT)
A Form with 2 text boxes (YEAR_START and YEAR_END) and a command button with a Macro assigned (an OpenReport Macro). The Macro is as follows:

[QRY_COST_BY_PERIOD]![YEAR]>[Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_START]<[Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_END]

Basically when I type in a year to year say 2013 and 2015 it opens my report with the chart, but the chart still shows all years and not just the filtered ones I have asked for.

I basically want the query to filter to the year range I type in and then open the report with the new query. I cant get my head around what I am missing?

Any help would be majorly appreciated!



Thanks!

Nick

[CJ_London]

your filter as described returns any record where year is greater than -1

[Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_START]<[Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_END]

this bit will always return -1 (or true)


try

[QRY_COST_BY_PERIOD]![YEAR]>[Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_START] AND [QRY_COST_BY_PERIOD]![YEAR]<[Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_END]

or

[QRY_COST_BY_PERIOD]![YEAR] BETWEEN [Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_START] AND <[Forms]![FRM_TEMPREJECTS_BY_PERIOD_FILTER]![YEAR_END]

Note also that YEAR is a reserved word, so strongly recommend changing it to something more meaningful such as 'CostYear'

[Chapster5]

Appreciated the very quick response mate. Much appreciated.

Just entered the first Macro you put and it never came up with any errors which is good. Saved the Macro and ran the form typed in 2010 and 2014 in the boxes and when I click run my report opens but the chart still shows all years in the query. My head hurts! Urgh. Tried the second Macro when I go to save Access pops up saying there was an error parsing the expression apparently?

Any clue mate?

Cheers!

[CJ_London]

I just used the name etc you provided and was correcting the syntax

Regret I don't use macros as among other things they are difficult to debug and haven't used 2003 for nearly 10 years and can't remember anything about charts in 2003.

does your macro open the report and you are just applying a criteria?

in vba you would use docmd.OpenReport "somereport",,,"criteria here"

I presume in a macro you have the filter in the Where section of the macro? (where it should be)

I also assume your report has a recordsource of QRY_COST_BY_PERIOD?