that isnt what I meant. I didnt say to use the textbox name, I meant actually copy the entire string that you used.

you will have to check to make sure i got the right amount of closed parens

=iff(Sum((NZ([Stop Time],0)-NZ([Start Time],0)-((NZ([Meal 1 In],0)-NZ([Meal 1 Out],0))+(NZ([Meal 2 In],0)-NZ([Meal 2 Out],0))))),>40,40,Sum((NZ([Stop Time],0)-NZ([Start Time],0)-((NZ([Meal 1 In],0)-NZ([Meal 1 Out],0))+(NZ([Meal 2 In],0)-NZ([Meal 2 Out],0)))))

Tried that too, same problem. I am attaching the DB to hopefully answer more questions. Select the employee with OT and open Report 1. I added two text boxes, the first returns 1 if over 40, otherwise 2. The second is the complete formula. Looking at the first textbox, it returns 2, the second result is 1.973611111. TotalHours is 47:22.

 TC.zip ( 296.42K ) Number of downloads: 2

I do not have 2010 so I have reached the limit of being able to help.

It's 2007

Gentlemen,

What "seems" to be the problem is in the formula, you are saying greater then the standard number 40 and your are comparing it to a value in HoursMinutes. You understand? What to do, I don't know...Yet.

RAZMaddaz

Change your formula to the following:

=IIf(Sum((NZ([Stop Time],0)-NZ([Start Time],0)-((NZ([Meal 1 In],0)-NZ([Meal 1 Out],0))+(NZ([Meal 2 In],0)-NZ([Meal 2 Out],0)))))>Hour(40),1,2)

Notice the Hour function I used.

RAZMaddaz

Here is a copy of your dbase with the change made.

This post has been edited by RAZMaddaz: Mar 15 2012, 01:24 PM

Brilliant. I renamed the textbox CheckOT that has the 1 or 2 in it. Now i have a problem with OT textbox.
=IIf([CheckOT]=1,[TotalTime]-Hour(40),Null)
and
=IIf([CheckOT]=1,HoursMinutes(Sum((NZ([Stop Time],0)-NZ([Start Time],0)-((NZ([Meal 1 In],0)-NZ([Meal 1 Out],0))+(NZ([Meal 2 In],0)-NZ([Meal 2 Out],0))))))-Hour(40),Null)
Tried with and without the Hour, gives me a #Error.

Where is this OT textbox? Is this the Text Box named Text106, which is under the other text box in the Report Footer?

 TC.zip ( 299.24K ) Number of downloads: 3


Also, had to use "40:00" to get the text106 box to display 40:00

Does not matter which employee I choose, result on the iif formula is always 1.

Sorry, I'm an idiot and didn't try the other Employees. Use the following instead:

=IIf(Sum((NZ([Stop Time],0)-NZ([Start Time],0)-((NZ([Meal 1 In],0)-NZ([Meal 1 Out],0))+(NZ([Meal 2 In],0)-NZ([Meal 2 Out],0)))))>TimeSerial(40,0,0),1,2)

Got the 40:00 to show if in Overtime, otherwise it shows TotalTime. I named the textbox RegTime
=IIf([TotalTime]>TimeSerial(40,0,0),HoursMinutes(TimeSerial(40,0,0)),[TotalTime])

Still can't get OT to work.
=[RegTime]-[TotalTime] gives me #Error

btw, nowhere near idiot level. I would have never thought of TimeSerial

If you enter the following:

=IIf([TotalTime]>[CheckOT],1,2)

This should work, then change the one and two to either Text Box name.

FYI, I need to go now, we can work on this tomorrow.

RAZMaddaz

Alringt, I have a textbox that will stop at 40:00, [RegTime].

Tried
=HoursMinutes(TimeSerial([TotalTime],0,0))-HoursMinutes(TimeSerial([RegTime],0,0))
=TimeSerial([TotalTime],0,0)-TimeSerial([RegTime],0,0)
=HoursMinutes([TotalTime])-HoursMinutes([RegTime])
=[TotalTime]-[RegTime]

All #Error
[TotalTime] =HoursMinutes(Sum((NZ([Stop Time],0)-NZ([Start Time],0)-((NZ([Meal 1 In],0)-NZ([Meal 1 Out],0))+(NZ([Meal 2 In],0)-NZ([Meal 2 Out],0))))))
[RegTime] =IIf([TotalTime]>TimeSerial(40,0,0),HoursMinutes(TimeSerial(40,0,0)),[TotalTime])