Case code in report not working

[jkfeagle]

I am trying to get a report to give an hourly average for each record it lists. The catch is that the average could be based on an 8 hour shift or a 12 hour shift depending on the shift designation from the query. I have put code in the report's Activate event to handle it but it is only calculating for the first record and putting that value in each record's average. Below is my code:

Sub Report_Activate()

Select Case Me!txtShift

Case "A"
Me!txtHourlyAve = Me!MetersProduced / 8
Case "B"
Me!txtHourlyAve = Me!MetersProduced / 8
Case "C"
Me!txtHourlyAve = Me!MetersProduced / 8
Case "M"
Me!txtHourlyAve = Me!MetersProduced / 12
Case "E"
Me!txtHourlyAve = Me!MetersProduced / 12

End Select

End Sub

Anyone have any ideas? Thanks!

 

[Rich]

Use the OnFormat or Print event