Change Series Name in Legend in Chart on Report

[Kirsti]

Hi, I have a column chart on my report, and my legend contains 2 series.

Currently they are: SumofHO and AvgofDays

This isn't so meaningful to the end user, I would like to change them to be "Total Number of Offers Made" and "Average Days Taken to Offer".

Is there a way I can do this (maybe using VBA)?

Any help appreciated.

[aytee111]

Presuming that your row source is a query, you can rename the fields in the query. In front of the field in the query design, put the Total name you want and then a colon (. This will rename the columns/legend to whatever you want. I can't remember what is required when you have spaces, you may need to enclose the name in quotes.

[Kirsti]

Thanks Aytee,

I have tried this, but because I'm calculating the total & the average of the fields in the report, I still get "SumOf" and "AvgOf" prefacing the series name, so I was hoping that there was a way to change the series name with VBA.

Perhaps the only answer is to calculate the sum & avg in the query first.

[jimmywalks]

Thanks Aytee,

I have tried this, but because I'm calculating the total & the average of the fields in the report, I still get "SumOf" and "AvgOf" prefacing the series name, so I was hoping that there was a way to change the series name with VBA.

Perhaps the only answer is to calculate the sum & avg in the query first.

When you are selecting the series names in the chart wizard (either in the data or series boxes), double click the name of each field once you have dragged them into the box. This will pull up a menu of options (Sum, Avg, etc.) including a "None" option. Just choose "None" and the SumOf or AvgOf will not appear in your chart anymore.

[Kirsti]

Well, this is an old one that has bugged me for ages, and I have just figured it out! Probably not news to anyone else, but just in case...

In the Row Source field of the Properties box, you can just change the series name, as you would in a SELECT query.

i.e. change: SELECT[Field1],Sum([Field2]) AS [SumOfField2]) FROM [qry1]
to: SELECT[Field1],Sum([Field2]) AS [MyFieldName]) FROM [qry1]

So simple!