Single Dynamic Report Question

[fastball16]

I'm trying to create a dynamic report where a user would select a date range of sales activity from a separte form. If a user selects a 2 year period, I want a report to populate with two columns of activity (ex. 2010 vs. 2011). If a user selects a 3 year period, I want the same report template to create 3 columns (ex. 2010 vs. 2011 vs. 2012) based on the underlying query.

Is this possible? I don't want to create multiple report templates because a user may have an infinite time range (1 year -> 10 years) and separate reports will lead down a slippery slope of creating inidividual reports for each permutation if I have > 20 years of sales data/query.

Thanks.
aC.

 

[sxschech]

You should be able to do this with one report. How will the user supply the years? A drop down combo box, hand entered in a text box.

Would they enter a start year and then a number to indicate how many years? Something like:
2010 and 2 would mean that they wanted 2010 and 2011
2010 and 3 would mean that they wanted 2010, 2011, 2012

You also mentioned that the report will show cols based on the number of years, are you using a crosstab query or a select query with all the cols prepopulated and it is a matter of telling the report to "hide" the "unneeded" cols.

 

[fastball16]

I have a form right now with 2 textboxes (FromDate / ToDate).

Based on the dates, the VBA coding currently pulls all activity between the dates. Since the dates can be of one or more years, I'm not familar with creating dynamic reports that will be flexible to accommodate total sales numbers.

So I need to know what kind of coding and report setup that will allow one report template to display the following scenarios based on date inputs:

1)
Unit-Company | 2010 Sales | 2011 Sales | 2012 Sales
2)
Unit-Company | 2010 Sales | 2011 Sales
3)
Unit-Company | 2011 Sales | 2012 Sales