I have a report that shows employees names as a grouping, and in the detail section displays data from a crosstab which breaks up departments and sales totals per month. The column headings are all the last 12 months in order and row headings of departments.
What I'm stuck on is every month I have to manually change the data source for the text boxes to move the months over. I also have to change the labels to match.
So the first text box was Oct 2012, second Nov 2012 and so on. I then change the first box to Nov 2012 second to Dec 2012 and so on.
I am looking for a solution where maybe I can put vba in the datasource for the first box to collect the data from the specific month that I wish to start on the table. The queries that the report is collecting the data from updates each month to reflect the correct months for the report. However when running the report it presents with the error due to not recognising eg: Oct 2011, as this month was in the last report but no longer in the current query as it now has Nov 2012 (being 12 months of data to date).
If anyone can assist or need more info to assist please let me know.
Thanks in advance