Sorting in a Report by Color

[Jag Girl]

I have a report in Access that gives the average score by skill that each teacher received.

For instance, in the body of the report I have Skill, TotalPointsPossible, TeacherAverage.

I have some VBA programming in the formatting of the detail that colors the background of the TeacherAverage based on the results, so if the TeacherAverage is between 1 and 10 the background color is red. If the TeacherAverage is between 10.1 and 20, the background color is yellow, and if the TeacherAverage is betwwn 20.1 and 30, the background color is green.

How can I then sort the body of the detail by color, so that all the red averages show up first, then yellow, then green.

Is this possible?

Thank you.

[puppydogbuddy]

I have a report in Access that gives the average score by skill that each teacher received.

For instance, in the body of the report I have Skill, TotalPointsPossible, TeacherAverage.

I have some VBA programming in the formatting of the detail that colors the background of the TeacherAverage based on the results, so if the TeacherAverage is between 1 and 10 the background color is red. If the TeacherAverage is between 10.1 and 20, the background color is yellow, and if the TeacherAverage is betwwn 20.1 and 30, the background color is green.

How can I then sort the body of the detail by color, so that all the red averages show up first, then yellow, then green.

Is this possible?
Thank you.

I don't believe you can order by color directly, but you can accomplish the same end result with this>>>>>> Order By TeacherAverage.

You did not post any code, so I'm assuming that you know where to insert the Order By clause. If you have any problems, let me know.

[Jag Girl]

I don't believe you can order by color directly, but you can accomplish the same end result with this>>>>>> Order By TeacherAverage.

You did not post any code, so I'm assuming that you know where to insert the Order By clause. If you have any problems, let me know.

One thing I didn't put in my post is the color coding is based on the total points possible and the teacher averages. So, if a skill has a TotalPointsPossible of 30, then the color coding would be red if the TeacherAverage was between 1 and 10, yellow if the TeacherAverage was between 10.1 and 20, and green if the TeacherAverage was between 20.1 and 30. Then if another skill has a TotalPointsPossible of 20, then the color coding would be red if the TeacherAverage was between 1 and 7, yellow if the TeacherAverae was between 7.1 and 14, and green if the TeacherAverage was between 14.1 and 20. So I can't just sort by the TeacherAverage. I know that somehow I need to assign the background colors with a value and sort by that value, but I just don't know how to go about it or where to put it. Any help would be appreciated.

This is the code that I am using the in the On Format Event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me![TotalPointsPossible] = 30 And Me![Average] >= 1 And Me![Average] <= 10 Then
Me.Average.BackColor = vbRed
ElseIf Me![TotalPointsPossible] = 30 And Me![Average] >= 10.1 And Me![Average] <= 20 Then
Me.Average.BackColor = vbYellow
ElseIf Me![TotalPointsPossible] = 30 And Me![Average] >= 20.1 And Me![Average] <= 30 Then
Me.Average.BackColor = vbGreen
ElseIf Me![TotalPointsPossible] = 20 And Me![Average] >= 1 And Me![Average] <= 7 Then
Me.Average.BackColor = vbRed
ElseIf Me![TotalPointsPossible] = 20 And Me![Average] >= 7.1 And Me![Average] <= 14 Then
Me.Average.BackColor = vbYellow
ElseIf Me![TotalPointsPossible] = 20 And Me![Average] >= 14.1 And Me![Average] <= 20 Then
Me.Average.BackColor = vbGreen
Else
Me.Average.BackColor = vbWhite
End If

[puppydogbuddy]

One thing I didn't put in my post is the color coding is based on the total points possible and the teacher averages. So, if a skill has a TotalPointsPossible of 30, then the color coding would be red if the TeacherAverage was between 1 and 10, yellow if the TeacherAverage was between 10.1 and 20, and green if the TeacherAverage was between 20.1 and 30. Then if another skill has a TotalPointsPossible of 20, then the color coding would be red if the TeacherAverage was between 1 and 7, yellow if the TeacherAverae was between 7.1 and 14, and green if the TeacherAverage was between 14.1 and 20. So I can't just sort by the TeacherAverage. I know that somehow I need to assign the background colors with a value and sort by that value, but I just don't know how to go about it or where to put it. Any help would be appreciated.

This is the code that I am using the in the On Format Event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me![TotalPointsPossible] = 30 And Me![Average] >= 1 And Me![Average] <= 10 Then
Me.Average.BackColor = vbRed
ElseIf Me![TotalPointsPossible] = 30 And Me![Average] >= 10.1 And Me![Average] <= 20 Then
Me.Average.BackColor = vbYellow
ElseIf Me![TotalPointsPossible] = 30 And Me![Average] >= 20.1 And Me![Average] <= 30 Then
Me.Average.BackColor = vbGreen
ElseIf Me![TotalPointsPossible] = 20 And Me![Average] >= 1 And Me![Average] <= 7 Then
Me.Average.BackColor = vbRed
ElseIf Me![TotalPointsPossible] = 20 And Me![Average] >= 7.1 And Me![Average] <= 14 Then
Me.Average.BackColor = vbYellow
ElseIf Me![TotalPointsPossible] = 20 And Me![Average] >= 14.1 And Me![Average] <= 20 Then
Me.Average.BackColor = vbGreen
Else
Me.Average.BackColor = vbWhite
End If

I can't find any documented examples that do what you want. Maybe one of the following methods might work. Have not tested and do not know if BackColor property is exposed to grouping/sorting routines. Let me know what happens if you try it. If "Average.BackColor" does not work, try "Average" & .BackColor

Private Sub Report_Open(Cancel As Integer)
 
Me.GroupLevel(0).ControlSource = "Average.BackColor"
 
End sub
 
Or this:
 
Private Sub Report_Open(Cancel As Integer)
 
Me.OrderBy = "Average.BackColor"
 
Me.OrderByOn = True
 
End Sub
 

[Jag Girl]

I can't find any documented examples that do what you want. Maybe one of the following methods might work. Have not tested and do not know if BackColor property is exposed to grouping/sorting routines. Let me know what happens if you try it. If "Average.BackColor" does not work, try "Average" & .BackColor

Private Sub Report_Open(Cancel As Integer)
 
Me.GroupLevel(0).ControlSource = "Average.BackColor"
 
End sub
 
Or this:
 
Private Sub Report_Open(Cancel As Integer)
 
Me.OrderBy = "Average.BackColor"
 
Me.OrderByOn = True
 
End Sub
 

I tried both and neither worked. The first option you gave me came back with error message "CALC expression (Average.BackColor) is invalid". If I changed it to "Average" & .BackColor it told me "Compile error: Invalid or unqualified reference" The second option gave me the error message "Column (BackColor) was used in a CALC expression but is not defined in the rowset"

[puppydogbuddy]

I tried both and neither worked. The first option you gave me came back with error message "CALC expression (Average.BackColor) is invalid". If I changed it to "Average" & .BackColor it told me "Compile error: Invalid or unqualified reference" The second option gave me the error message "Column (BackColor) was used in a CALC expression but is not defined in the rowset"

Jag Girl,
OK, that pretty well establishes that you can't order your data using field properties and BackColor is a field property.

What you can do is create an alias column (see below) in your source query that assigns a row value in the alias column, and then you will be able to sort on the alias column:

ColorRank: IIf(([TotalPointsPossible] = 30 And [Average] Between 1 And 10) Or ([TotalPointsPossible] = 20 And [Average] Between 1 And 7), “Red”, IIf(([TotalPointsPossible] = 30 And [Average] Between 10.1 And 20) Or ([TotalPointsPossible] = 20 And [Average] Between 7.1 And 10), “Yellow”, IIf(([TotalPointsPossible] = 30 And [Average] Between 20.1 And 30) Or ([TotalPointsPossible] = 20 And [Average] Between 14.1 And 20), “Green”, “White”)))

Once you have the above column in your Query, then you can order by ColorRank in the open event of your report as follows:

Private Sub Report_Open(Cancel As Integer)
 
Me.OrderBy = "ColorRank"
 
Me.OrderByOn = True
 
End Sub