Back Color for Access97

[lansel]

I have a report in Access 97 which is group by employee number and has the following totals for each employee

SumOfStdHrs
SumOfTotalUnits
Units/Hr (this is calculated SumOfTotalUnits/SumOfTotalUnits
Goal (I enter this number weekly)
85% (This is calculated [Goal]*.85

If the Units/Hr is less than 85% of the Goal, I want the backcolor to be red. I don't know exactly how to do this.

If ([SumofTotalUnits]/[SumOfStdHrs]>[Goal]*.85

Then [SumofTotalUnits]/[SumOfStdHrs].Backcolor =red

Most of the answers I found for the backcolor didn't have the calculations and I don't know code.

Thanks,

 

[The_Doc_Man]

Most of the answers I found for the backcolor didn't have the calculations and I don't know code.

Time to learn VBA.

You can do this in the _Format event for the report section where this item occurs. Your {section_type}_Format event gets called BEFORE you display the section. At this point you can apply a .BackColor to the textbox in question. You didn't show whether the items were integer or real so I'm going to show the math ambiguously. I'll shorten the names, too. I'll also use the RGB function which is in one of the libraries available through Office. But you can skin this cat any one of a dozen different ways.

On the report, select the section where this item occurs. Right click on the section (not on a control in that section). In the Events list, find the OnFormat or whatever it is called. Make it invoke an Event Procedure by selecting the appropriate DropDown option. This automagically invokes the code editor. The code you want MIGHT look like this.

Private Sub Whatsisname_Format()

If ( 100 * [SumTotUnit]/[SumStdHrs] ) > 85 Then
[Ratio].BackColor = RGB( 255, 255, 255 )
Else
[Ratio].BackColor = RGB( 255, 240, 240 )
End If

End Sub

 

[lansel]

Thanks for taking the time to respond. I will work on this report the next chance I get. I really appreciate all the help I get from the forum.