VBA to change Access Graphs

[Jeff]

Hello,

I have a simple Table called tblCustomers and a field in there call Status.
The values of Status can only be A, B, C or left blank (null)

I have made a Pie Graph that shows the breakdown of the number of each Status.

the Report name I called 'rptCustomersStatusGraph'
the Graph name I called 'graCustomersStatus'
I set the Graph Record Source as

SELECT Status,Count(*) AS [Count] FROM tblCustomers GROUP BY Status

All seems to be working ok except:

Question 1:
In the Legend, for the Piece of the Pie that has the Blank (null) values it
is Labeling it as "Slice 1" I cant figure out how to change this so the when
the Report prints it has for that Label something like "{Blank}" not "Slice 1"
How can I rename it & have it stick to that name ?

Question 2:
I would like to be able through VBA code to change the colors of each piece
of the pie different time when the Report is run not always use the same
colors.
How can this be done with VBA code. I am assuming it has something to do
with the Graph Object but I can't figure out how.

Any help would be greatly appreciated !

Thank you,
Jeff


I have 2 questions

 

[Duane Hookom]

Change your sql to replace null status values with some value:
SELECT Nz(Status,"NA") As TheStatus ,Count(*) AS [Count] FROM tblCustomers
GROUP BY Nz(Status,"NA");

I create a similar chart in Excel and then turn on the macro recorder, make
changes to the chart, turn off the recorder, copy the code, and paste it into
the report.