Variable number of fields in tabular report

[JohnLanc]

I need to create a report with a number of time periods running across the report horizontally. Each time field will correspond to a field in a dynamically created table. My problem is that each time I create the table it will have a different number of fields. How can I create a report to handle this variability?

Your help would be greatly appreciated!

 

[DougP]

this MAY work
Can you add data to a table that has the most number of fields you will ever use?
In other words say 10 is the worst case scenario of your data nothing will ever exceed 10 fields.

Base your report on that table and add and delete records to it.
I did this to make a calendar report
Each month has a different number of days and each 1st day starts on a different day such as Monday Friday or whatever.
So my table had 45 columns, each month could use up to 45 squares on a calendar layout.
At the beginning of printing, all records in the table were deleted.
Then refilled with that months numbers.
If the first of the month was on a Monday like this month then I put the data into the 2nd column the first column was left blank this month.
If a month started on a Sunday is would be filled in.
Then the records were added and a generic report was printed.

Hope this makes sense


DougP, MCP

 

[MichaelRed]

If the variable number of (date) fields are the result of a crosstab query, you can fix the fields in a couple of ways.

One approach is to use the unique property of the crosstab "ColumnHeadings". This works well for things like weekdays where you may not have data for a specific "DAY" - but want to retain the column to maintain compatability with the report.

Another approach is to create a seperate recordset with the columns of interest for the header and do an outter join of that field into the (crosstab) query's other source on the similar field. Use the "alternate" recordset as the column field and ALL entrien in that recordset will have columns in the output.

Note that the two approaches cannot co-exist and return the "And" of the set. The "ColumnHeadings" property will over-ride the join.

As a copmpletely different, you can dynamically create a report at run time based on the query results. to see a SIMPLISTIC example of this, use the search feature of these fora with the term "CreateReport". You shoud find several threads and at least one of them should have some code (procedure) which accepts a recordset and generates a simple tabular report from the columns of the recordset. It does not include any sorting/grouping or report aggregates, howevver htese could be added by the intrepid programmer.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JohnLanc]

MichaelRed,

Thanks for the advice. I would like to create the report programmatically but could not find any threads as you suggested. I don't suppose you have any sample code available?

Thanks again

John

 

[JohnLanc]

MichaelRed,

I found the createreport reference!

I have managed to create a report programmatically now using your code, which worked great - thank you. But to complicate things further I now need to reference a second table in the report - any ideas?

Thanks

John

 

[MichaelRed]

" ... referencing a second table ... " is somewhat vague, so any ideas I might have are quite general. You, obviously, can set the ControlSource for the cotrols at/during hte creation of the report, per the sample code you have already found/used, so I would think that by simple extensionof that code, all you need is the name of the recordset and field?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places