Add Conditional Formatting to a Chart

[ItsRoland]

I have a few Pie Charts in a report, which display percentages of "High", "Medium", and "Low" in a table. The Chart is based off a query.



I had set the color of "High" to red, "Medium" to yellow, and "Low" to green, and that was all fine.

However, I recently noticed that since "Low" wasn't currently a value in the table, and thus not displayed on the chart, the chart had "High" as red and "Medium" as green.

The only solution I see to this is to add conditional formatting to the chart, but as far as I can tell you cannot do that in access. Is there any way I can somehow accomplish the same result through vba code? Or perhaps another way of preventing this issue altogether?

Any help is appreciate! Thanks in advance.

[isladogs]

There may be other better ways, but try using Nz(CountField,0) to ensure all values exist even if zero

[ItsRoland]

Where would I place the Nz() Function in my code?

I can't find an event like OnOpen or OnLoad for the chart itself, and I'm not sure how to point towards the chart from the report itself, i.e. Me.chartName.something = Nz(...), if thats even the right approach here.

As I'm sure you're aware by now, seeing as you also helped me yesterday, I haven't had to mess with charts up until now. I apologize if I'm missing anything obvious.

[June7]

Review https://www.accessforums.net/showthread.php?t=29178

Is the query a CROSSTAB? Post query and sample data.

[ItsRoland]

SELECT tScenarios.riskRating, IIf([riskRating]="Low",1,IIf([riskRating]="Medium",2,IIf([riskRating]="High",3))) AS Expr1, IIf([Expr1]=1,"Low",IIf([Expr1]=2,"Medium",IIf([Expr1]=3,"High"))) AS Expr2
FROM tScenarios;

Sample Data:
riskRating........Expr1......Expr2
Medium.............2............Medium
High..................3............High
Medium.............2............Medium

I don't believe this query is a CrossTab, but I could be wrong. I didn't specifically create it to be one.

There's probably a better way of doing this, but this is how I got it to work.

Expr1 displays a 1 if riskRating is low, 2 if medium, 3 if High, and Expr2 displays low if Expr1 is a 1, medium if 2, high if 3. The chart wasn't letting me create it if no numbers were involved, and If I tell it to count riskRating instead of Expr2 it just displays nothing, so if this is a weird waying of going about it, that's why.


The link June7 posted is a bit closer to what I'm looking for I think. I tried to change it up to work for my own chart, and I managed to get it to change the color of one of the slices in the pie chart, but after that I got the following error:

"Unable to get the SeriesCollection property of the Chart class"

Here's what the code looks like after I made my changes:

Dim chtObj As Object
Dim j As Integer
Dim strType As String
Dim c1 As Long, c2 As Long, c3 As Long


c1 = RGB(100, 100, 100)
c2 = RGB(100, 250, 250)
c3 = RGB(100, 200, 100)


Set chtObj = Me.chrtRisk.Object


For j = 1 To 3
    
    strType = chtObj.SeriesCollection(j).Points(1).DataLabel.Text
    Debug.Print strType
    chtObj.SeriesCollection(j).Points(1).Interior.Color = Switch(strType = "Low", c1, strType = "Medium", c2, strType = "High", c3)
    
Next

[isladogs]

It isn't a crosstab query
June's point was that if the query was a crosstab you can specify column headings (even if one or more is missing).

Somewhere you are counting the values shown in the pie chart.
As I deleted your example after the previous thread I don't know how you're getting the counts.
My idea was to use Nz where the count was done.
if that's not feasible, then I would play around some more with the code you just posted

[ItsRoland]

The count is being done in the charts row source. Here's the row source for the chart:

SELECT qOverallRisk.[riskRating], Count(qOverallRisk.[Expr2]) AS CountOfExpr2 FROM qOverallRisk GROUP BY qOverallRisk.[riskRating] HAVING (((qOverallRisk.[riskRating])<>""));

I didn't think I could use Nz here, but if I'm wrong then that's fantastic lol

[June7]

No, Nz won't help. To make sure every category is present even when there is no data would require including a dataset of all possible categories, which in your case is 3. This can be accomplished with a lookup table, however I expect you don't have one for your 3 categories. You could put 'dummy' records in your data table or possibly a UNION query to assure there is always at least one record of each category. But that will mean the count will be off by 1 so would have to subtract one from the count calc.

[ItsRoland]

I'm not entirely sure how to do that, could you give me some example code to go off of?

If not I can attempt it and post results.

[June7]

Possibly:
SELECT tScenarios.riskRating, IIf([riskRating]="Low",1,IIf([riskRating]="Medium",2,IIf([riskRating]="High",3))) AS Expr1, IIf([Expr1]=1,"Low",IIf([Expr1]=2,"Medium",IIf([Expr1]=3,"High"))) AS Expr2
FROM tScenarios
UNION ALL SELECT "Low", 1, "Low" FROM tScenarios
UNION ALL SELECT "Medium", 2, "Medium" FROM tScenarios
UNION ALL SELECT "High", 3, "High" FROM tScenarios;

Then in the aggregate query: Count(qOverallRisk.[Expr2]) - 1

[isladogs]

Just to confirm June's previous answer.. Nz won't help here as your query won't have a record for Nz to work on
Personally I'd go for the tRisk table approach and link that to tScenarios using an outer join to get all 3 fields BUT you will need to modify the Expr1 & Expr2 fields in your query

[ItsRoland]

So I tried June7's approach, and now the graph isn't displaying the values properly. Its showing Low at 30%, Medium 36% and High 34%, but Low should be 0%.

maybe I placed the "Count(qOverallRisk.[Expr2]) - 1" in the wrong place? You mentioned to put it in the aggregate query but I'm not sure how to do that since after changing the sql code I can no longer view the query in design view (right-click -> design view pulls up the sql view).



Also, @ridders52, I'm not entirely sure how to set up an Outer Join. Up until this point I've achieved everything I needed to through vba code, so I've only scratched the surface of using queries.


I apologize for needing such detailed explanations, and really appreciate the help you two have given me thus far.

[isladogs]

I started to produce a solution then realised your Expr1 & Expr2 would need changing so binned it.
As you've got started on the UNION approach, I'll leave you in June's care.

[June7]

So qOverallRisk is now a UNION query? Right, UNION cannot be opened in design view. However, another query (the aggregate) referencing the UNION should be able to.

Ooops, just tested UNION ALL. I see what may be causing your percentages to be off. Revise:
SELECT tScenarios.riskRating, IIf([riskRating]="Low",1,IIf([riskRating]="Medium",2,IIf([riskRating]="High",3))) AS Expr1, IIf([Expr1]=1,"Low",IIf([Expr1]=2,"Medium",IIf([Expr1]=3,"High"))) AS Expr2, "D" AS Source
FROM tScenarios
UNION SELECT "Low", 1, "Low", "X" FROM tScenarios
UNION SELECT "Medium", 2, "Medium", "X" FROM tScenarios
UNION SELECT "High", 3, "High", "X" FROM tScenarios;

[ItsRoland]

The revised code above seems to be working properly now, thank you!

If you wouldn't mind, could you explain to me what "D" and "X" are doing in the code?


Also, I tried using your code for my other charts, which do the same thing as the riskRating chart, only for different columns (all of which also only contain "high", "medium", or "low"). All I did was change "riskRating" to the name of the corresponding field. I did this for two charts, one for "residualRisk" and the other for "ncmRisk". The "residualRisk" chart isn't displaying percentages properly, the same issue you just fixed on the "riskRating" chart. The "ncmRisk" chart is displaying percentages properly, but unlike the other two charts it isn't setting "low" to 0 instead of null, so the coloring is off again.

This is really strange, considering all I did was create new Union Queries (1 for "residualRisk", 1 for "ncmRisk") using the same sql that you posted, changing "riskRating" to the corresponding field name, and got 2 different issues. I was really hoping I was done struggling with charts after your code worked perfectly for the first one.

Below is the sql and row source for each of the charts, if you can see any issues.

Residual Risk:

SELECT tScenarios.residualRisk, IIf([residualRisk]="Low",1,IIf([residualRisk]="Medium",2,IIf([residualRisk]="High",3))) AS Expr1, IIf([Expr1]=1,"Low",IIf([Expr1]=2,"Medium",IIf([Expr1]=3,"High"))) AS Expr2, "D" AS Source
FROM tScenarios
UNION SELECT "Low", 1, "Low", "X" FROM tScenarios
UNION SELECT "Medium", 2, "Medium", "X" FROM tScenarios
UNION SELECT "High", 3, "High", "X" FROM tScenarios;

SELECT qOverallResidual.[residualRisk], Count(qOverallResidual.[Expr2]) AS CountOfExpr2 FROM qOverallResidual GROUP BY qOverallResidual.[residualRisk] HAVING (((qOverallResidual.[residualRisk])<>""));

ncmRisk

SELECT tScenarios.ncmRisk, IIf([ncmRisk]="Low",1,IIf([ncmRisk]="Medium",2,IIf([ncmRisk]="High",3))) AS Expr1, IIf([Expr1]=1,"Low",IIf([Expr1]=2,"Medium",IIf([Expr1]=3,"High"))) AS Expr2, "D" AS Source
FROM tScenarios
UNION SELECT "Low", 1, "Low", "X" FROM tScenarios
UNION SELECT "Medium", 2, "Medium", "X" FROM tScenarios
UNION SELECT "High", 3, "High", "X" FROM tScenarios;

SELECT qNcmRisk.[ncmRisk], Count(qNcmRisk.[Expr2]) - 1 AS CountOfExpr2 FROM qNcmRisk GROUP BY qNcmRisk.[ncmRisk] HAVING (((qNcmRisk.[ncmRisk])<>""));