Solved

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

Posted on 2010-11-08
8
521 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
ID: 34087003

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
ID: 34087030
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
ID: 34087561
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:fastsue
ID: 34091478
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 34093236
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
ID: 34093484
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
ID: 34094690
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
ID: 34096258
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

786 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