Conditional Statement to Fill in Proper Textbox!!

[mspeppa19]

I have a report that tracks employees total hours. I have textboxes Hr1, Hr2, Hr3....that connect to the "Hours Worked" database. Then I added Over1, Over2, Over3...as textboxes onto the report (they have no control source) What I wanted to do was say, if Hr1 (hours worked 1) is greater than 8 hours, than the value of Hr1 would be 8 and the value of Over1 would be the difference. For example, an employee worked 10 hours one day. Hr1 = 8; Over1 = 2. Here is my code and i'm getting errors. I put it on my report under the "On Open" event. Thanks

Private Sub Report_Open(Cancel As Integer)
If Hr1.value > 8 Then
Hr1.value = 8
Over1.Value = 2
Else
Hr1.value = Hr1.value
End If
End Sub

 

[CosmoKramer]

I've just done something similar to this to track the hours a resident works in a hospital. In the query that is the record source for the report, add 2 calculated fields for each day:

DutyHoursHr1: IIf([Hr1]>8,8,[Hr1])

and

OTHoursHr1: IIf([Hr1]>8,[Hr1]-8,0)

Use these fields in your report. Let me know if this helps....

 

[mspeppa19]

Okay great, that makes sense but where do I put the code? My report isn't based off of a query but is based off of a table. I tried to create a query and when I put the "IF" statement in the Criteria section of the query i got an error message that said "undefined IF statement" Thanks again

 

[CosmoKramer]

If using a query, put the code in the Field: box in the query grid, not the criteria. As an example, in:

DutyHoursHr1: IIf([Hr1]>8,8,[Hr1])

DutyHoursHr1

becomes the field name.

Or, you could put

=IIf([Hr1]>8,8,[Hr1])

as the text box's control source on the report directly.....