# Calculate Weekly Payroll Hours

I have a payroll form with employees & their time in(emp1ti) & time out(emp1to) that is filled out daily.  I have an expression to calculate an employee's daily hours minus 1/2 hour for lunch in the control source property of a box used just for display: =(DateDiff("n",[emp1ti],[emp1to])/60)-0.5

I need to also display total weekly hours on the form.  I have a table field (emp1twh) that I would like to write the daily time as an integer to.  I would like to add to that field every day's daily hours until it is Monday again, then overwrite the field.  The new value should be displayed as soon as the new timein and timeout are entered.  I have the following psuedo code, but am unsure where to put it and how to update my table field:

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)

Any help is much appreciated.  I am new to VBA.

Thanks,
jessica@tridewb.com
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Applications DeveloperCommented:
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 :)
Applications DeveloperCommented:
My employees got 45 minutes for lunch.
Yours would be 00:30:00" AS Lunch

Alan :)
Applications DeveloperCommented:
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;
Database and Application DeveloperCommented:
A very good solusion Alan.  Mike
Author Commented:
Where does this go?  I keep getting a syntax error.
Applications DeveloperCommented:
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.

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:

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 :)

Commented:
SELECT Table.id,sum(([Table]![emt1to]-[Table]![emp1ti])*24 - 0.5) AS Expr1
FROM Table
where table.id = 1
group by table.id;
Author Commented:
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!
Author Commented:
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.
Applications DeveloperCommented:
Hi Jessica,

I think I've got the picture now!

You didnt mention whether employees had a unique ID, so I assumed that they did and called mine empID.
I also assumed the name of the table that your form is sourced on is called tblEmployees.
These two assumptions may need to modified to fit.

Here is a Sub Procedure that you can assign to the After Update Event for your form field emp1to.

So after a value is entered in emp1to this sub should fire and produce a message box showing hours worked for the current employee for the Week-Block beginning from the Monday prior to emp1ti to the Monday after emp1ti. allowing for the possibility that empt1i is a Monday. Phew!

Private Sub emp1to_AfterUpdate()
Dim iWeekday As Integer
Dim fDate As Date
Dim tDate As Date
Dim dTotalHoursWorked As Double
Dim sWhere As String
Dim sql As String

If Nz(emp1ti, 0) = 0 Then: Exit Sub
If Nz(emp1to, 0) = 0 Then: Exit Sub

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

' Establish the range
fDate = emp1ti
iWeekday = Weekday(fDate)

' establish lower date range
' calculate date for previous monday from emp1ti
' if emp1ti is monday use emp1ti
While iWeekday <> 2
iWeekday = Weekday(fDate)
Wend
fDate = Format(fDate, "dd-mmm-yyyy")

' establish upper date range
tDate = Format(DateAdd("d", 7, fDate), "dd-mmm-yyyy")

' Build the query string
' Filter between date range (fDate and tdate) and EmpID

sWhere = " WHERE (((tblEmployees.emp1ti) Between #" & fDate & "# And #" & tDate & "#"
sWhere = sWhere & ") AND ((tblEmployees.empID)=" & empID & "))"
sql = "SELECT Sum((DateDiff('n',[emp1ti],[emp1to])/60)-0.5) AS calcDailyHours From tblEmployees"
sql = sql & sWhere

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
dTotalHoursWorked = rs!CalcDailyHours
End If

MsgBox "This employee (" & empID & ") has worked: " & dTotalHoursWorked & " this week"

' You can set your field value here
' Me.emp1twh = dTotalHoursWorked

Set rs = Nothing

End Sub

Alan :)

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Applications DeveloperCommented:
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.