[nuschool33]

Ok all, bear with me. I have created a simple database that holds agent statistics for a daily scorecard. There are 6 different types of employees and each type has a certain criteria to meet.

I.E. - An entry level employee must meet a quota of 8 calls per hour (CPH), If he/she meets it then they "Meet Expectations" if it is below then they "Need Improvement"

I have a report tht lists all employees, their employee type (entry, intermediate, advanced, etc) and their "CPH".

I would like their CPH to be color coded based on meeting criteria or not. It would be easy if they all had to meet the same criteria (I would use conditional formatting)but since there are 6 different types, is there a way to write code for this.

Example in plain words:
IF "employee type=entry level"
Then follow these guidelines to color code values.

I know this may be choppy so let me know what detailed information you would need from me. If you want to really help I wouldn't mind sharing my db by email and having you take a look, it is just a very basic database.

This is the code I have so far, i am very new to VB and am not sure if this is correct. It compiles just fine, and I can open the report after saving this code, but it still does not work. Got any ideas???

Here is what i have so far...let me know what questions youhave or if there is anything you need to help me out. Thanks

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim CCS_HSSType As String
Dim CPH As Integer

   If Me.CCS_HSSType.Value = "CCS Entry Level" Then
      If Me.CPH.Value > 9 Then
         Me.CPH.Properties(FillColor) = 38
      Else
         Me.CPH.Properties(FillColor) = 27
      End If
   ElseIf Me.CCS_HSSType.Value = "CCS Intermediate Level" Then
      If Me.CPH.Value > 10 Then
         Me.CPH.Properties(FillColor) = 38
      Else
         Me.CPH.Properties(FillColor) = 27
      End If
   ElseIf Me.CCS_HSSType.Value = "CCS Advanced Level" Then
      If Me.CPH.Value > 12 Then
         Me.CPH.Properties(FillColor) = 38
      Else
         Me.CPH.Properties(FillColor) = 27
      End If
   ElseIf Me.CCS_HSSType.Value = "HSS Entry Level" Then
      If Me.CPH.Value > 8 Then
         Me.CPH.Properties(FillColor) = 38
      Else
         Me.CPH.Properties(FillColor) = 27
      End If
   ElseIf Me.CCS_HSSType.Value = "HSS Intermediate Level" Then
      If Me.CPH.Value > 9 Then
         Me.CPH.Properties(FillColor) = 38
      Else
         Me.CPH.Properties(FillColor) = 27
      End If
   ElseIf Me.CCS_HSSType.Value = "HSS Advanced Level" Then
      If Me.CPH.Value > 10 Then
         Me.CPH.Properties(FillColor) = 38
      Else
         Me.CPH.Properties(FillColor) = 27
      End If
    End If
End Sub 

Please let me know if you can help.

Thanks, Nuschool33

[Jimmy the Hand]

I would suggest to give conditional formatting a try. Here's how I imagine it.
Create a public function on a VBA module. E.g.

Public Function MeetCriteria(sType as String, iCPH as integer) as Boolean
   MeetCriteria=false
   If (sType = "CCS Entry Level") and (iCPH > 9) then MeetCriteria = True
   If (sType = "CCS Intermediate Level") and (iCPH > 10) then MeetCriteria = True
   If (sType = "CCS Advanced Level") and (iCPH > 12) then MeetCriteria = True

   If (sType = "HSS Entry Level") and (iCPH > 8) then MeetCriteria = True
   If (sType = "HSS Intermediate Level") and (iCPH > 9) then MeetCriteria = True
   If (sType = "HSS Advanced Level") and (iCPH > 10) then MeetCriteria = True
End Function

Then make a conditional formatting for the CPH object on report. Set it's default fillcolor to 27. In the conditional formatting, use expression, like

MeetCriteria([reports]![reportname]![CCS_HSSType];[reports]![reportname]![CPH])=True

And set up the format (fillcolor = 38) for the case when criteria is met.

I tried this on a hastily created form, as I didn't have the patience to create a report, and it worked. I suppose, it should work with reports, too. And, in the conditional formatting expression, I used ";" to separate the arguments of MeetCriteria function , but maybe English Access requires "," instead. I'm curious if this works, please give me feedback.

[OBP]

The attached database I did for someone else to show formatting a report.
Perhaps it will help you.
It has 3 reports, the 1st one shows how to change the background colour to green
The 2nd doesn't work I think, it is the original sent to me, it is a Conditionally formatted form turned in to a report, which causes the formatting to be lost..
the 3rd is more advanced and is using VBA to step through the fields on a report, this would be used in conjunction with the 1st one, where you have lots of fields and want to use a single Sub Routine or function call to change the colours.

[nuschool33]

OBP,

You nailed it, Once I replaced with the fields from my tables and change the ";" to "," it worked right away.

Thanks for all your help. I also got it to work with just VB code as well, I looked at the sample DB that you attached and my syntax was incorrect on some lines of my VB code.

So what I am trying to say is Thanks...it worked both ways.

Thanks Again, I ,ay need yopu again in the future.

nuschool33

[OBP]

Glad to help, can you mark the thread as solved please?