Solved

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

Posted on 2010-11-08
8
501 Views
Last Modified: 2012-05-10
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
Comment
Question by:fastsue
  • 4
  • 2
  • 2
8 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility

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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
hit submit key too fast
Orange: iif([VALUE]<250,0,[VALUE])
Green: iif([VALUE]<500,0,[VALUE])
Yellow: iif([VALUE]<750,0,[VALUE]).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:fastsue
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 

Author Comment

by:fastsue
Comment Utility
Thanks puppydogbuddy, but how do I get the bar on the chart to use these colours?
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now