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)
Milkus1Asked:
Who is Participating?
 
Milkus1Author Commented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.