Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate Weekly Payroll Hours

Posted on 2003-11-21
11
Medium Priority
?
2,753 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:jlbillingsley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9800628
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 :)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9800636
My employees got 45 minutes for lunch.
Yours would be 00:30:00" AS Lunch

Alan :)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9800648
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;
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9801051
A very good solusion Alan.  Mike
0
 

Author Comment

by:jlbillingsley
ID: 9804164
Where does this go?  I keep getting a syntax error.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9804680
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 :)


0
 
LVL 8

Expert Comment

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

Author Comment

by:jlbillingsley
ID: 9812206
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!
0
 

Author Comment

by:jlbillingsley
ID: 9819578
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.
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 2000 total points
ID: 9821295
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
  Dim rs As ADODB.Recordset
 
  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
    fDate = DateAdd("d", -1, fDate)
    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
 
  Set rs = New ADODB.Recordset
  rs.Open sql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
 
  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 :)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9821317
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
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question