Repo of CrossTab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have made a cross tab query, for which I want to make a report. When I make
a report it works fine. But later when the Row And Column increases or
decreases, it show errors on report. And generally in future the column & row
of my data will increase and decrease. Every time I dont want to make a
report for the same thing and deseign Neither I want my used to do so.

Please advise a suitable solution for this.

Regards.

Irshad
 
Yes, reports cannot cope with columns that disappear from the source query.

Option 1: Set column headings
If you know at design time what all the possible columns will be (e.g. 1 to
12 for months), you can open your query in design view, open the Properties
box (View menu), and enter all the values into the Column Headings property.
The crosstab will then generate all columns, even if there is no data for
them

Option 2: Programmatically assign columns.
If you do not know what the column headings might be at design time, you can
do it programmatically if you are comfortable with VBA. Create a text box
for as many columns as you could need, and name them something like Text0,
Text1, Text2, ... Save the report with nothing in its RecordSource.

In the the Open event of the report, get a list of all the possible column
names (typically by an OpenRecordset()). You can then create the entire
Crosstab query statement as a string variable, place all the possible column
heading names in the PIVOT clause, and assign the statement to the report's
RecordSource. Assign each of the possible headings as the ControlSource of
the text boxes. If you have more text boxes than you need for this
particular time, set their Visible property to No, and set the Left and
Width of the text boxes so they space out appropriately. You probably want
to set the Caption of the labels above the columns as well. If you do end up
with more column headings than you allowed text boxes for, you have a choice
of cancelling the report, or displaying a warning that some columns were
dropped.

Option 3: Create a report in design view.
Instead of relying on a fixed number of columns to cope with every future
case of the query, you could open the report in design view, and
CreateControl() to get enough for the report.

While #3 overcomes the limitation of #2 regarding the number of text boxes,
a serious disadvantage is that you cannot create an MDE of your application.
In my view, it is much better to create all the controls you could need at
design time, and document what the limit is for the report i.e. using option
2 if option 1 is not enough.
 
Back
Top