colour change in a bar graph (1 Viewer)

[virencm]

Hi,

I have a bar graph on a report in ms access that represents 4 different risks ie count on the y axis and risk type on the x axis (low, moderate high and extreme)

Question: How do i get the each bar to automatically represent the colour based on the risk colours below?

Low: Green
Moderate : Blue
High : Yellow
Extreme: Red


Thanks

 

[JHB]

Open your form/report in design view, double click on the chart object, click on the bar/column you want to change the colour and then right click, choose "Format Data Series" and choose the tab "Patterns", then choose the colour you want from "Area" group.

 

[virencm]

Thanks for your response JHB. I have formatted the bars in design view to represent the colours required for each risk.

Your solution works perfectly if the query result returns all four risks., but If the query returns 3 risks or even 2 risks than it chooses the first two or three colours that were formatted in the design view and it is not necessarily representative of the color codings for the risk.

Is there a way to hard code the risk values to represent the color associated using vba?

Cheers!

 

[JHB]

...
Is there a way to hard code the risk values to represent the color associated using vba?

That is more or less what my suggestion do.
Then you've to go the VBA-way.
Could you show the result from the query when only 2 or 3 column is returned - by a printscreen. And also the SQL string for the graph's "Row source".
But does people not ask if there sometimes only are only 2 or 3 column and other times are 4?

 

[virencm]

Hi JHB,

The reason for different risks is that when a machine assessment is done it could return all 4 risks depending on the machine use hazards identified or in other instances a machine might show only 2 or 3 risks depending on the hazards input.

I have atttached all the requested information below.

thanks again for your time.

 

[JHB]

I have made some sample for you - open form "Test1" or open form "Test2" in the attached database.
I'm sure you can improve the code or make it on another way, but I'll leave to you!