Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-08
8
Medium Priority
?
563 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

916 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