How do I select a range of field names dynamically for a report?

[mejohnnyt]

My Columns (field names) are w/e dates. I need to produce a report each week
that has the last 6 rolling week ending dates. Can I set up some sort of
array or something to select those dates and only display the last 6 weeks?

 

[AndyB via AccessMonster.com]

Hi,

What I tend to do in these circumstances is put a combo box on my form (call
it something like 'DateSearch') which looks up the date fields where you can
select the first date of your report.

Then build a query on which to base your report and in the criteria for the
date field put filter something like:

Forms![My Form]![Date Search] - 1

Build or modify the report with your query as the Record Source.
Put a command button on the form to open the report.

What you're actually saying in this query is print all records with a date
later than one day less than your selected date - That way your selected date
is included in the report.

If you open your report in Prieview mode, it'll probably open behind your
form, so use your command button to set the form's Visible property to False
and set your report's 'On Close' property to make your form visible again.

Andy

 

[Duane Hookom]

Assuming your actual table structure is properly normalized, you can use the
solution found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would need
to change the months to weeks.