One report required for 13 months rolling...

[NKA]

Can anyone help me please?

I have been running a report (company sick) for several months but have been manually changing the fields to suit the current month!

I would like to set the report up ONCE and then have some code which will dynamically change the fields on the report.

Eg. In November, I run the report which has the following column headings:

Name, Emp_No, Nov04, Oct04,.........Nov03

When it comes to December, I have to design the report by deleteing Nov03, moving all the other fields across one and including Dec04.

I am sure there must be a way to do this with code... I just don't know how!!

The report is driven by a query.

I look forward to some help on this one - it will save me so much time each month!

Many thanks


NKA

The answer's always easy - if you know it! ;-)

 

[dhookom]

Check this solution faq703-5466.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[seaport]

It is better to create this report by outputting data into an Excel worksheet.
You can write code to dynamically set the column heading (for all 13 columns) first.
Then you have two choices.
1. You can create the ADO recordset for one column, and then copy data (using CopyFromRecordset method) to first-month column in the worksheet. Then repeat the process for the rest of months.
2. You can create a query with 13 months columns on the run-time. Then copy the data to the worksheet.

I prefer option 2.

Hope this helps.

Seaport

 

[dhookom]

No offense seaport but did you look at the FAQ suggested? Having to push or pull the data to Excel is not necessary when a nice report can be created all in Access.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[seaport]

Dhookom,

I think we submitted responses at the same time and I was not comparing my answer to yours.

I checked the FAQ. It is what NKA needed. It is only my personal preference to use Excel as the reporting tool because of the flexibility of formatting.

Seaport