Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

Change bar on bar chart to a different colour depending on result in Access 2007

Hi, I have created a bar chart based on a query.  Each bar represents a different element result i.e Nitrogen  50, Potassium 250, the next record may have Nitrogen 250, Potassium 750. As the image shows.
Example GraphI can obviously change the bar colour but I want to be able to get it to change depending on the element result.  Eg. a result of 250 the bar would be orange,  500 green, 750 yellow.
Can this be done?  I hope my explaination makes sense.
Any help you can give will be much appreciated.
fastsue
0
fastsue
Asked:
fastsue
  • 4
  • 2
  • 2
1 Solution
 
puppydogbuddyCommented:

Try modifying the statement in the query to include the following columns>>>>>>
 "Orange: iif([VALUE]<250,0,[VALUE])"
 "Green: iif([VALUE]<500,0,[VALUE]
 "Yellow: iif([VALUE]<750,0,[VALUE]).
0
 
puppydogbuddyCommented:
hit submit key too fast
Orange: iif([VALUE]<250,0,[VALUE])
Green: iif([VALUE]<500,0,[VALUE])
Yellow: iif([VALUE]<750,0,[VALUE]).
0
 
Jeffrey CoachmanMIS LiasonCommented:
You can try something like this:
Notes:
1. The Recordset in the code is the Rowsource for the Chart.
2. See here for other RGB colors:http://www.tayloredmktg.com/rgb/
3. If this is in a Report, you *Must* explicitly open the report in Print Preview.
(Not Report View, which is the default)

Since I don't know the specifics of your data, or how the chart is designed, you will have to modify this to work in your database.

;-)

JeffCoachman
Access-EEQ26599865SetBarChartGra.mdb
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
fastsueAuthor Commented:
HI, sorry I don't think I explained it well.  
This is the line in my query that determines the value in the recordset used in the chart for OptN.
IIf([fieldname]='Deficit',100,
IIf([fieldname]='Low',250,
IIf([fieldname]='Norm',500,
IIf([fieldname]='High',750,
IIf([fieldname]='Excess',1000))))) AS OptN
Each page of my report is a different sample number (recordset) and the chart is linked by the sample number.   The chart on each page is working correctly but what I want to do programatically is change the colour of the bar for OptN.  If OptN = 100 the bar stays the default colour, if OptN = 750 the colour of the bar changes to Yellow, If OptN = 1000 the bar colour changes to red.
fastsue
 
0
 
puppydogbuddyCommented:
I think all you need to do is Add 1 more column to your query and reference for the color:
IIf([OptN]<100,0,[OptN] As Orange,
IIf([OptN]<750,0,[OptN]) As Yellow,
IIf([OptN]<1000,0,[OptN]) As Red
0
 
fastsueAuthor Commented:
Thanks puppydogbuddy, but how do I get the bar on the chart to use these colours?
0
 
puppydogbuddyCommented:
Forget my previous post........Try adapting this code to the specifics of your MS Access Datasheet.

Private Sub Form_Load()   'should also work for the report open event
Dim chtObj As Object
Dim x As Long
_______________________________
Set chtObj = Me!YourChartObject
x = 1

Do Until x = chtObj.SeriesCollection.Count
    With chtObj.SeriesCollection(x)
         If Not IsNothing(chtObj.Application.DataSheet.Cells(x + 2, 1)) = True Then
          Select Case x
             Case 1
                   chtObj.SeriesCollection(x).Interior.ColorIndex = 5        'blue
             Case 2
                   chtObj.SeriesCollection(x).Interior.ColorIndex = 4        'green
             Case 3
                   chtObj.SeriesCollection(x).Interior.ColorIndex = 3        'red
             Case 4
                   chtObj.SeriesCollection(x).Interior.ColorIndex = 6        'yellow
             Case Else
                 'do nothing
          End Select
'          MsgBox " x Value " & x
'          MsgBox " Cell Value " & chtObj.Application.DataSheet.Cells(x + 2, 1).Value
          x = x + 1
        Else
'          MsgBox " x Value " & x
'          MsgBox " Cell Value " & chtObj.Application.DataSheet.Cells(x + 2, 1).Value
          x = x + 1
        End If
    End With
Loop

'clear datasheet and chart variable
chtObj.Application.DataSheet.Cells.Clear
Set chtObj = Nothing

End Sub

_____________________
0
 
Jeffrey CoachmanMIS LiasonCommented:
Perhaps puppydogbuddy has a better understanding of this than me.

For me, I would like to see a sample of this database.
This way we do not spend our valuable time guessing...
(For ex:, nobody knew that this was a multipage report, nobody knew that you had a "Formula in the query, ...etc)

I am not understanding the Formula you are using or how it is being used in conjunction with the chart you posted.

JeffCoachman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now