Solved

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

Posted on 2010-11-08
8
550 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

628 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