Updating X-axis labels in a chart on a report

[ryp]

I have data in a chart over four groups that are just labeled A, B, C and D in the underlying data. The table wants to label the x-axis of the chart SumOfA, SumOfB, SumOfC, and SumOfD. Ideally I would like to drive these labels by text boxes on a form, but I can't even figure out how to change them manually. I got to the data table in the chart designer and changed them there, and they'd show as changed in design view, but then they weren't changed in report view. I don't mind using VBA if that's required. Thanks for any help.

[June7]

AFAIK, can't change these labels in the chart properties. Have to do it in the query that is the RowSource for the chart. Show the sql statement for the RowSource.

[ryp]

Thanks!!! I kept double clicking the graph and getting that graph/chart editor, and I was missing that if I just clicked once on the graph, I could open the properties of the graph and see the query there. That helped a lot and solved the problem--I appreciate it! I edited the query to show as follows:

SELECT [billable],Sum([A]) AS [Curr Week],Sum([B]) AS [Previous Week],Sum([C]) AS [Two Weeks Prior],Sum([D]) AS [Three Weeks Prior] FROM [qryCR_ClientFourPeriod] GROUP BY [billable];

This worked, but ideally I'd like it to look up the actual dates from a form and use them in the label (so instead of "Curr Week" it could say "Week of 1/14/13"). To achieve this I tried this in the Open event of the report:

Me.graphHours.RowSource = "SELECT [billable],Sum([A]) AS [Week of " & Format(Forms!frmDates!txtStartA, "mm-dd") & "],Sum([B]) AS [Previous Week],Sum([C]) AS [Two Weeks Prior],Sum([D]) AS [Three Weeks Prior] FROM [qryCR_ClientFourPeriod] GROUP BY [billable];"

Unfortunately this gave me Run-time error '2455': You entered an expression that has an invalid reference to the property RowSource. My research leads me to think this can't be in the Open event. The On Load didn't work either. Any suggestions on that?

[June7]

Did you try just putting that SQL with the concatenated variable directly in the chart RowSource?

[ryp]

That's a great idea--didn't think of that, but unfortunately it gave an error too. 'Week of " & Forms!frmDates!txtStartA & " is not a valid name. I started wondering if it was a quotes issue, so I tried a few variations (removing the format function, using single quotes, and escaping the quotes), but unfortunately that didn't work either.

For anybody playing with this type of thing, I did learn that you don't always see an error until you try Print Preview. I would change the SQL, view it in Report View, and nothing would change. Then I'd try to do Print Preview, and that's when I'd see the error or see the SQL change.

[June7]

Ooops! Just realized that can't be done because field names can't be dynamic in Access queries and since the chart RowSource is an Access query, out of luck.

I do use VBA to set the RecordSource of a report in the report Open event. Never tried setting the RowSource of a chart. Perhaps in Format event of the section the chart sits in. However, I have doubts that will work.

True, some report events trigger only in PrintPreview or direct to printer - Format is one of those events. All of my reports are set with PrintPreview as default view.

You might just have to use a textbox with an expression that references the form control to show the date. You can position the textbox on top of the chart control. Use an opaque background and block out the 'Curr Week' label.