Crosstab Chart (1 Viewer)

Could someone please assist with the following issue with a chart (on a form) that is based on a crosstab query. Row source for the chart is a crosstab query that counts the number of records based on the level of risk. (High, Medium and Low). I'd obviously like the bar chart colours to appear as red, amber and green respectively.

However, when there are no Medium records to count, the colours of the bar chart get mixed up and the High records don't appear as red etc.

I'm guessing it's a VBA solution (that's what I've based my extensive search on to date) in order to programmatically control this dynamic chart.

One more nice to have would be to have the ability to apply a gradient/shading to the barcharts in order to jazz it up a little.

Any tips/sample code/links would be very much appreciated

To make sure that all column headings appear in your crosstab you need to hard code the headings in the columnheadings property of the query.

Thanks David

That'll work geat for the example that I posted - I'll give it a try later.

I also have another crosstab where additional values can be added. In the event that someone adds a value, the crosstab will also have to be updated -this won't happen very often and with my memory, there is little chance of me remembering to hard code the new values as column headings. Any clever way of overcoming this particular scenario?