Conditions on form calculated textbox

[Chris75 via AccessMonster.com]

Hello,

In a form called Schedule (based on the table Schedule) I have the following
textboxes that are bound to the table Schedule:

Time In, Time Out, Time Off, Anomaly and Overtime

I also have an unbound textbox called HRS that gives me the result of the
following equation:

=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime]

The above result gives me the daily hours that someone worked.

The formating is decimal, as an example.
16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0

I also added another figure for lunch the hour
[16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0] - 1.0 = 9.0

The issue is that some employees work a variable schedule and may not take
the lunch hour. Would anyone know how to set up a condition to substract or
not count the lunch hour based on the amount of hours worked.

The idea is:

If [Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] >5 Then -1 Or
=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] <5 Then 0

Thank you.

 

[John Spencer]

=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] -
IIF([Time Out]-[Time In]-[Time Off]-[Anomaly]+[Overtime]>5,1,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jackie L]

Chris,

I don't know if you are still in the design process but I am not sure your
table is set up in a way that would best serve the purpose of time card
reporting. A normalized setup would be each time record with the employee,
date, type of record (regular, vacation, etc), time in and time out. Then,
the time between punches would be added for the total time for that day.
Overtime would be calculated at a difference between the hours for the day
and the standard. If an employee punched out for lunch and back in on
return, the sum of the records would automatically reflect that. You could
also do the lunch out time based on the total time spent (i.e. 8.5 hours
means .5 hours of lunch). You could either calculate
the time in reports or run a utility that would append the lunch record as a
negative time entry. The same could be done for the overtime to recategorize
the earnings but still leave the actual punches in tact.

There are many ways to achive a specific need in this case, the import part
is that you are not restricting your data to a certain number of fields.
Relational databases are designed to avoid such an issue.

 

[Chris75 via AccessMonster.com]

Thank you very much John!

=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] -
IIF([Time Out]-[Time In]-[Time Off]-[Anomaly]+[Overtime]>5,1,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

[quoted text clipped - 26 lines]

Thank you.