Calculate Weekly Payroll Hours

Hi Jessica,

Something like this may do:

SELECT [tblTimeClock].[IN], [tblTimeClock].[Out], Format(DateAdd("s",DateDiff("s",[In],([Out])),(Date())),"hh:nn:ss") AS Hours, "00:45:00" AS Lunch, Format(DateAdd("s",DateDiff("s",[In],([Out])),(Date()-#12/30/1899 0:45:0#)),"hh:nn:ss") AS Net
FROM tblTimeClock;


Just change the fieldnames:

IN > emp1ti
Out > emp1to
tblTimeClock > YourTable


Alan :)

My employees got 45 minutes for lunch.
Yours would be 00:30:00" AS Lunch

Alan :)

SELECT [tblTimeClock].[emp1ti], [tblTimeClock].[emp1to], Format(DateAdd("s",DateDiff("s",[emp1ti],([emp1to])),(Date())),"hh:nn:ss") AS Hours, "00:30:00" AS Lunch, Format(DateAdd("s",DateDiff("s",[emp1ti],([emp1to])),(Date()-#12/30/1899 0:30:0#)),"hh:nn:ss") AS emp1twh
FROM tblYourTable;

A very good solusion Alan.  Mike

Where does this go?  I keep getting a syntax error.

Hi Jessica,

If you want to use it as a calculated field in a report or form add this to the controlsource property for the calculated field.
=Format(DateAdd("s",DateDiff("s",[emp1ti],([emp1to])),(Date()-#12/30/1899 0:30:0#)),"hh:nn:ss")

If you want to include it in the form or reports recordsource you need to edit the recordsource for the form or report by clicking the build button [...] alongside the Record Source property in the form/report properties dialog.This will invoke a query builder based on the current recordsource.

To add a calculated field in query design view, in a new field add:
emp1twh: Format(DateAdd("s",DateDiff("s",[emp1ti],([emp1to])),(Date()-#12/30/1899 0:30:0#)),"hh:nn:ss")

Then set the controlsource of your calculated field in your form/report to: emp1twh

If you are still having difficulty, thats OK, just let me know and we can take this one step at a time from the top.
Initially we aim to provide a quick response, treating questions with greater points associated with greater urgency. I am more than willing to walk through this with you.


Alan :)

SELECT Table.id,sum(([Table]![emt1to]-[Table]![emp1ti])*24 - 0.5) AS Expr1
FROM Table
where table.id = 1
group by table.id;

Maybe I wasn't clear in my original question.  This appears to calculate only the hours between the employee's time-in & time-out.  My expression takes care of this.  I also like that my expression returns an integer number.  The part I need assistance with is a running total of total weekly hours worked on the form.  I want to add to the total weekly hours field until it is Monday again, and then I want to overwrite the field and start over.  I have the following pseudo code:

My fields are emp1ti(employee time in), emp1to(employee time out), and emp1twh(employee total weekly hours)

If DatePart("d", emp1ti[,2]) = 2
   Then emp1twh=(DateDiff("n",[emp1ti],[emp1to])/60)-0.5
Else
    emp1twh=emp1twh+((DateDiff("n",[emp1ti],[emp1to])/60)-0.5)

Thanks!

If it's Monday, (start over) write the integer difference between employee time in and employee time out minus 1/2 hour for lunch.
Otherwise, add what already exists in that table field: employee total weekly hours, to the integer difference between employee time in and employee time out minus 1/2 hour for lunch.

Hi Jessica,

You can drop this line I was using it for testing, I created a temp field in the table to calculate hours on a daily basis:

 Me.emp1tdh = (DateDiff("n", [emp1ti], [emp1to]) / 60) - 0.5



Alan