Can you provide more details? How are your column headings generated?
--
Duane Hookom
MS Access MVP
Thanks for the information about dynamic column headings. I was able
to
view
some of the information you posted to the query and found it very
useful.
The problem I'm having is similar. I'm working on a trending report
and
would like to choose the year the report starts. Any suggestions?
Thanks
Frank Bobak
:
Try this one
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
MS Access MVP
Hi Duane,
This site pops up a lot but I don't see any file on this
site.
Do
you know if it exists anywhere else?
Rgds
Nobleman
:
Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution
is
very
efficient, uses less code than Option 2, and is much more flexible.
--
Duane Hookom
MS Access MVP
As you found, the report does not cope with column names that
come
and
go
as they do in a crosstab query.
Some options:
Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always
present),
you
can type them into the Column Headings property of the query
(Properties
box, in query design view.) For example, if the your column
heading
field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of
the
year,
you could enter all the numbers in Column Headings.
Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with
the
maximum
number of columns you will ever need. The text boxes are unbound,
and
the
report's RecordSource is blank.
Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's
RecordSource,
and assign each of the column heading fields to the Control
Source
of
the
appropriate text box. Set the Left and Width of each box, and the
Caption
of the label above the column, and hide the unused text boxes and
labels.
This takes a bit off effort, but does produce very flexible
reports.
3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it
up
for
whatever columns you determine you need at runtime.
I do not recommend this 3rd approach, as a) it is more complex;
b)
it
does
nothing that option 2 cannot do; c) it stops you generating an
MDE;
d)
it
bloats the database; e) it leaves the user with questions about
saving
the
report when they close it.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
I am using MS ACCESS XP. I setup an crosstab query and want to
create
a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any
solution
that
will generate the crosstab dynamic column?
I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic
columns...
Thanks
Jean