Need help substracting 1

[allong]

Hello everyone. In my report I need to a field that will show me all hours over 40. I not quite sure how it is done.

 

[fredk]

Can you elaborate on that? Do you want your report to show only records where the hours field is greater than 40????

Need some more info.

Fred

 

[allong]

I'm sorry Fred. I need to get the hours over 40. In other words My work week is 40 hours. Everything over 40 is overtime. I am trying to calculate overtime hours for each individual with more than 40 week.

 

[fredk]

The query that is the source of your report, can you give me the fields that are in that query - Then we can work something out - thanks!!!

Fred

 

[allong]

Good afternoon Fred, here are the fields:

EmployeeName;EmployeeID;CustomerName;CustomerID;ScheduleDate;StartTime;EndTime;CostCenter;PayRate;TotalHrs;OvertimeHrs.

 

[cheiboy]

What kind of expression do you have in your OvertimeHrs criteria?

Try this not in your query, but in the form as a text box

= Sum ( [PayRate]*[TotalHrs]*1.5)

A shot in the dark but see how that turns out.

 

[CosmoKramer]

OvertimeHrs can be a calculated control in your report. Add an unbound text box and set its control source to something like this:

IIf([TotalHrs] > 40,[TotalHrs] - 40, 0)

 

[fredk]

CosmoKramers suggestion is on the mark. However, I would put the criteria in the query so I could sort on it.

I would add these two fields to your query:

Regular Hours: IIf([totalhours]<40,[totalhours],40)

Overtime Hours: IIf([totalhours]>40,([totalhours]-40),0)

Now you have the two fields that actually total to the correct number of hours

This works fine for me and I can sort by these new fields (for example, sort the report by most overtime hours in descending order.

Hope that makes sense.

Fred

 

[allong]

Thanks everyone. Great job. Report working very well. Did not know what to do until you gave me the answer. So long for now and have a wonderful day.