Grouping date values in Office Web Components Chart

Question

How can I format & group dates in the category axes?

I try to do it with this code:

With ChartSpace.Charts(0).Axes(chAxisPositionCategory)
    .NumberFormat = "mmm yyyy"
    .GroupingType = chAxisGroupingManual
End With

but all that I get is "invalid parameter".

If I write this:

debug.print ChartSpace.Charts(0).Axes(chAxisPositionCategory).NumberFormat

I get "General"

and then if I write:

ChartSpace.Charts(0).Axes(chAxisPositionCategory).NumberFormat = "General"

it's "invalid parameter"!!!

This is VBA code in MS Access using OWC11. I've bound the data to an SQL Table with 3 columns:

Field1 varchar(200),
Date1 datetime, --can't use Date type or the chart won't recognise as date!!
Value1 int

I've been searching for help and others have this problem but no one has had an answer - except alternatives that don't fix the problem. I enve have a MS example that works in html but uses hardcoded data. I need to use all the time based X axis grouping functions but can't get past a simple number format.

add comment: discovered that this will now work:

.Charts(0).Axes(chAxisPositionCategory).CategoryLabels.PivotAxis.Fields(0).NumberFormat = "mmm yyyy"

still can't do the grouping though.

Answer 1

I realize this is old but since it is still unanswered and searchable:

You can group by dates in OWC11 by using the timescale option on the x axis:

ChartSpace.Charts.Axes[ChartAxisPositionEnum.chAxisPositionTimescale].GroupingType = ChartAxisGroupingEnum.chAxisGroupingNone;

The default behavior is to group the dates, use the GroupingNone enum to turn that off.

@David-W-Fenton: OWC11 is depreceated but is in extended support until 2014 and still downloadable. There has never been a MS replacement for them that has all the functionality. The chart controls for .NET 3.5 don't allow the end user to manipulate the chart in the same way. I would not use OWC in a website due to security issues, but I use them in internal WinForms apps. I don't know of another free chart control that allows the same level of runtime chart manipulation as OWC. (Sorry for adding a comment to an answer but I don't have the priviledge to comment yet).