We help IT Professionals succeed at work.

Ordering the seriescollection in an Access PivotChart form

I am using the chartspace object in my Access 2010 application.
It is not always easy to format the control via VBA, but up until this point I have managed.

The current issue I have is that my bar chart needs to display each series in a particular order
The chart displays along a time line in years, with a grouping for each year of 3 bars.

eg: low, base, high ....it current does it alphabetically :  base, high, low.

The recordset that populates the chart serves up the data in the right order, but when the chart displays the bars are sorted incorrectly.

eg: 2015 low   5.4
      2015 base 6
      2015 high  7.9
      2016 low   5
      2016 base 5.9
      2016 high  6.1

I need to use VBA to solve this issue, as the form presented to the user is readonly and doesnt allow direct changes to the chart. I dont know if this helps but the data is bound as

            .SetData chDimCategories, chDataBound, "YearNumber"
            .SetData chDimValues, chDataBound, "Rate"
            .SetData chDimSeriesNames, chDataBound, "RateTitle"  ' (low,base,high)
Watch Question

I have a work around that gave me the outcome I sort but I consider it fairly dodgy, happy to hear a more refined solution.

What I did was I changed the SQL to output - 1,2,3 instead of low,base,high. This means the internal chart data sorts numerically for the grouping on each year.
I then cycled through the seriescollection captions and replaced the 1,2,3 with the correct label, so the legend appearred to make sense to the user.

.Charts(0).SeriesCollection(icount).Caption = Replace(.Charts(0).SeriesCollection(icount).Caption, "1", "Low", 1, , vbTextCompare)