changing color of slices in a Pie Chart with VBA 1

[baboudi]

Hi all,

I have a query which calculates the 'health' status of a system e.g.
Red: 15%
Yellow: 35%
Green: 50%

This is used to plot a pie chart.
I manually changed the Pie Slices color to match the health description. I.e. The Red Slice will be Red, etc.

When changes are made to the system, hence affecting the health for example, Red: 0. In the Pie Chart I will have only 2 Pie Slices, which is fine...however, this messes up the colors of the Slices and they are assigned some other random colors

I have been looking but could not fine out how to assign each slice a specified color via vb.

Thanks in advance
babou

 

[dhookom]

I would make sure that a record for every color was included. I just did this with the Northwinds and generated a total freight value for customers by shipper. Some customers didn't use all three of the shippers so a piece of the pie was missing. I made sure my chart's Row Source query included each shipper by first building a cartesian query. I then left joined this cartesian query to the Orders table.

Without some table and field descriptions, I can't be of more assistance on how this could be implemented in your application.

Duane
Hook'D on Access
MS Access MVP

 

[baboudi]

thanks dhookom.

I gave your suggestion a go without success.

here is some more details
I have Query1 (qryPieChart_RawData) which calculates the status from the product of two other fields A and B:

Switch([A] * [B}>0 And [A] * [B}<5,"Green",[A] * [B}>4 And [A] * [B}<13,"Yellow",[A] * [B}>12,"Red",True,"ExistingControls ") AS healthStatus


I have Query2 (qryPieChartData) which then calculates the count of Green, Yellow, etc.

SELECT qryPiechart_rawdata.healthstatus, Count(*) AS TotalCount
FROM qryPiechart_dataraw
GROUP BY qrycat_piechart_rawdata.healthStatus;


this returns the values below for example which is then used for the piechart
Red: 15
Yellow: 35


I tried creating a new table with just one field that I used to left join with the query. Table: tblcolor
Field: Color (P Key)
Values: Yellow, Red, Green, Exisitng COntrols

Join Query
SELECT qryPiechartData.HealthStatus, Count(*) AS total
FROM tblColor LEFT JOIN ryPiechartData ON tblColor.Color = qryPiechartData.healthstatus
GROUP BY qryPiechartData.HealthStatus


However I was still having the ed and Yellow.

I removed the grouping and count(*), to see what was the result of the left join, and instead of having the values 'Green' and 'Existing Control' from the tblColor (which are the non-existant values from the qryPiechart_rawdata, i had the value 'Yellow'.

It seems that the join is not working too well on a calculated value.

Hope my explanation is clear enough...thanks for your help.
Babou

 

[baboudi]

found one problem with the queries. I will let you know once its fixed.

Switch([A] * [B}>0 And [A] * [B}<5,"Green",[A] * [B}>4 And [A] * [B}<13,"Yellow",[A] * [B}>12,"Red",True,"ExistingControls ")

This 'True' bit was assigning a default value. So this was removed and replaced with [A]* = 0

 

[baboudi]

Many thanks dhookom. Your solution did the trick