Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automate Column headings (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Column headings (2003)

    See if <post:=145,832>post 145,832</post:> helps.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2003)

    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

  4. #4
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2003)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Column headings (2003)

    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.

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2003)

    OK - I understand it better now
    Thanks Hans

    Robert

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2003)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Column headings (2003)

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

  9. #9
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2000)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Column headings (2000)

    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.

  11. #11
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2000)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Column headings (2000)

    See for example <post:=513,413>post 513,413</post:> or <post:=390,996>post 390,996</post:>.

  13. #13
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2000)

    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?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Column headings (2000)

    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)

  15. #15
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Column headings (2000)

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •