Automate Column headings (2003)

Topic

New Reply

I have designed a one page report (a page of 9 graphs) for a company’s “KPI’s” or Key result areas. The information comes from different sources and so I have used sub-reports, which all feed from different queries.
My queries are crosstab queries that include a similar expression ( Expr 1:Month([Date of Sale]) ) as a column heading to feed into graphs.
Then, in each of the 10 Query Properties, I set the column headings (1,2,3,4,5,6,7,8,9,10,11,12) so that the data feeds into the reports in the correct order. I set label headings ( “Jan Feb Mar…… etc) in the main report to pull it all together.

I now need to show this data on a 12 month rolling basis. I can replace the Jan, Feb and March 2005 data with relevant 2006 data OK, but now, each month, I need to change the order of the labels in the report and then go into every query to change the order of the column headings.

Can anyone suggest an easier solution or advise how I can change the column headings (and labels) by code.

Thanks
Robert

Reply | Quote

Replies

See if post 145,832 helps.

Reply | Quote

Thanks Hans

Seems to be exactly what I need. I now have to study this to work out how to build it into my application
Thanks
Rob

Reply | Quote

The example Hans refers to contains a subroutine (Sub GetData). When the report opens, there is an On Format Event on the report that contains the code “Call GetData.”

I was just wondering why it was set up this way. To simplyfy things, couldn’t the Sub GetData code be placed directly into the “On Open” event of the report. Is there some kind of benefits that I am missing?

Robert

Reply | Quote

GetData gets called from three event procedures, each time with a different argument:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Call GetData(acGroupLevel1Header)
End Sub

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
Call GetData(acPageHeader)
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Call GetData(acFooter)
End Sub

Instead of repeating the same code in all three event procedures, the code was placed in a separate procedure, with an argument to specify which section of the report is calling it. Moreover, the code runs for each group header, inserting different data each time. This could not have been done in the On Open event.

Reply | Quote

OK – I understand it better now
Thanks Hans

Robert

Reply | Quote

Please help – I’m completely stuck!

I need to produce monthly Customer reports on a 12 month rolling period. The report will show sales and returns (and other info) under 12 column headings “mmm yy” for previous 12 months up to current month.
My plan is to have a report with subreports that feed from crosstab queries. (Is this probably the best way ?)

The crosstab queries return data for all months (I just need the previous 12 months) and they do not currently return a month if there is no data. I can fix this by setting column headings in the query properies to “mmm yy” but this too manual because I will have a lot of crosstab queries. Also they are not in ascending order (April is first alpabetically).

I can put columns into ascending order if I sort by the formula Year([Date]*12 +Month([Date]), but these become column headings.

I don’t know which is the best method and so my attached example has crosstab queries set up in the two slightly different ways.

The code for rolling months(in a previous post) doesnt seem to work in Access 2000

I am very grateful for any help and ideas.
Thanks

Reply | Quote

The database you have attached is corrupt beyond repair – there is something very wrong with rptRollingMonths.

Reply | Quote

I am working in Access 2000. I took this example database from Woody’s lounge and converted it from an earlier version of Access. However, the report doesn’t return any data even though there is data in the table.

I would also like to format zero’s as blank.
(I need to add averages and graphs to the report and so l am wary of changing zero’s to blank strings in case it throws the numbers.

Thanks

Robert

Reply | Quote

VBA and SQL are US-centric, so you must make sure that literal dates are in mm/dd/yyyy format. This can be done by changing RepDate in the FindFirst and FindNext instructions to Format(RepDate, “mm/dd/yyyy’).
Formatting zeros as blanks will not help if you want to perform calculations, because it only changes the way the values are displayed, not the underlying values. You can leave empty values blank by changing the instruction Ctrl = 0 to Ctrl = Null.
See the attached version.

Reply | Quote

Thanks Hans

If I turn qryMonthlySales into a Parameter query the report fails to open. I get a Run time error message “Run-time error 3061error” and the report code fails at :-

Set rst = mydb.OpenRecordset(“qryMonthlySales”).

Presumably it cannot open the query because it cannot finfd the parameter. Does this need to be added to the code?

Thanks

Robert

Reply | Quote

See for example post 513,413 or post 390,996.

Reply | Quote

Thanks again Hans.

I also need to show categories of Sales by each Salesman. Could I tweak this code to capture the category data in the report?

Reply | Quote

You have to apply only two small changes:

1) Somehow, the GroupHeader0_Format procedure is not linked to the group header any more.
– Open the report in design view.
– Click on the group header for Category.
– Activate the Event tab of the Properties window.
– Click in the On Format event.
– Select [Event Procedure] in the dropdown list.
– Click the … button to the right of the dropdown arrow.

2) Since Category is the 2nd level group header (Salesman is the 1st level), you must change the instruction in the event procedure to

Call GetData(acGroupLevel2Header)

Reply | Quote

This doesn’t quite seem to work for me. In the database “Bob’s” sales show up twice in both categories?

Reply | Quote

You also need to extend the condition in the “Sales” case:

…
Case “Sales”
Dim strCondition As String
strCondition = “[rptMonth] = #” _
& Format(RepDate, “mm/dd/yyyy”) & “# and [Salesman] = ‘” _
& Me![Salesman] & “‘ And [Category] = ” & Me!Category
rst.FindFirst strCondition
…

Reply | Quote

Thanks once again Hans.
I altered your code slighly and placed “[” and “]” around “Category”

Sorry Hans one more question if I may.
This was my cut down database. In my full application ‘Category’ is an ID key but I need to report the Category Description.
After adding a linked table to the query I get a “Run Time 3070 error – Access doesn’t recognise my category names as valid field names.

Reply | Quote

Since you are now using a condition on a text field instead of a number field, you must put quotes around the value, just as for the salesman.

strCondition = "[rptMonth] = #" _
& Format(RepDate, "mm/dd/yyyy") & "# and [Salesman] = '" _
& Me![Salesman] & "' And [Category Name] = '" & Me![Category Name] & "'"

Reply | Quote

That works perfectly.
Thank you very much Hans – you’re an invaluable help.

Reply | Quote