access reports

[dimpie]

I am trying to run a report based on a crosstab query.

User inputs total number of months forcast the budget. I want to generate a
report dynamically as the col header is the month and the year.

how can i do this in the backend when the user clicks on preview button.

The crosstab query has the data, but the report uses only field that are
initially designed. Is there a way that i can program in such a way that the
report is generated based on the coloums in the crosstaab query?
Hope this is clear.

Thanka a lot for all the help!

 

[Marshall Barton]

I am trying to run a report based on a crosstab query.

User inputs total number of months forcast the budget. I want to generate a
report dynamically as the col header is the month and the year.

how can i do this in the backend when the user clicks on preview button.

The crosstab query has the data, but the report uses only field that are
initially designed. Is there a way that i can program in such a way that the
report is generated based on the coloums in the crosstaab query?

Instead of using the Month of a date field to as the column
headings use something like Month(date field) - Month(start
date). This way the field names will be 0, 1, 2, ... and
the report won't have to worry about the names being
different for different dates. Yor can reconstruct the real
month numbers in the header row by using text boxes with an
expression like =Month(start date) + 0, =Month(start date)
+ 1, =Month(start date) + 2, ...

 

[dimpie]

I am sorry, may be i was not very clear. It is kind of hrd to explain. Let me
try again.

total number of coloums in my report varies. It could be 2 or 6 or 10,
depending upon user entry.

so in my crosstab query, total coloums also varies . 2, 6, 10 or more.

when i use the crosstab query to generate the report, total cols in the
report varies too. usually, i hard code total cols expected in the report.
but now total header cols varies depending on the user.

Is there a way i can run a report in access where the total cols displayed
in different each time a report is run.
Please advise.

 

[dimpie]

actually, i think i got what you said. Thanks a lot. I wil try this out
tomorrow and will let you know. Thanks for all your help.

 

[Marshall Barton]

actually, i think i got what you said. Thanks a lot. I wil try this out
tomorrow and will let you know. Thanks for all your help.

There is always a maximum number of data fields in a report
and you need to make sure that users can not break things by
specifying more than the report is designed to deal with.

A simple way to take care of that is to specify the
allowable columns in the crosstab query's ColumnHeadings
property. After getting the "relative month numbers"
specified in my previous post to work, set the
ColumnHeadings to 0,1,2,3, ... up to however many months
your report can handle.

Dealing with blank month columns in the report when users
specify fewer than the maximum months is a separate problem
that can be addressed later. It can be faily messy so your
best bet is to just let the report display the empty
columns.