Microsoft KB Archive/237958

= ACC2000: How to Calculate Daily Hours Based on Clock In/Clock Out Times =

Article ID: 237958

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q237958



This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article shows you how to calculate daily hours worked, regardless of the number of times an employee clocks in or out.



MORE INFORMATION
Follow these steps to create a sample database that calculates the total hours an employee works on a specified day:   Create a new database, and then add the following tables to this new database:   Table: Employees Field Name: EmpID Data Type: AutoNumber Field Size: Long Integer Indexed: Yes (No Duplicates)

Field Name: EmpName Data Type: Text Field Size: 50 Indexed: No

Table Properties: Employees ---  PrimaryKey: EmpID

Table: EmpDates Field Name: DateID Data Type: AutoNumber Field Size: Long Integer Indexed: Yes (No Duplicates)

Field Name: MyDate Data Type: Date/Time Format: Short Date Indexed: No

Field Name: EmpID Date Type: Number Field Size: Long Integer Indexed: No

Table Properties: EmpDates ---  PrimaryKey: DateID

Table: EmpTimes Field Name: TimeID Data Type: AutoNumber Field Size: Long Integer Indexed: Yes (No Duplicates)

Field Name: TimeIn Data Type: Date/Time Format: Medium Time Input Mask: 09:00\ >LL;0;_ Indexed: No

Field Name: TimeOut Date Type: Date/Time Format: Medium Time Input Mask: 09:00\ >LL;0;_ Indexed: No

Field Name: DateID Date Type: Number Field Size: Long Integer Indexed: No

Table Properties: EmpTimes ---  PrimaryKey: TimeID  Create the following two relationships

 Employees to EmpDates (One to Many) on EmpIDEmpDates to EmpTimes (One to Many) on DateID EmpDates to EmpTimes (One to Many) on DateID

and then enable the following options for these relationships:  Enforce Referential Integrity</li> Cascade Update Related Fields</li> Cascade Delete Related Records</li></ul>

</li> Create a new query, add the following SQL statement as the source for the query, and then save the query as qryTimes:

SELECT TimeID, TimeIn, TimeOut, DateID, DateDiff("n",[TimeIn],[TimeOut]) AS CalcTime FROM EmpTimes;

</li>  Create three new forms that are based on the following information: <pre class="fixed_text">  Form: frmEmployees ---  Caption: Employees RecordSource: Employees

Text Box Name: txtEmpID ControlSource: EmpID Enabled: No

Text Box --     Name: txtEmpName ControlSource: EmpName

Form: frmDates --  Caption: Dates RecordSource: EmpDates

Text Box -     Name: txtMyDate ControlSource: MyDate

Text Box -     Name: txtDateID ControlSource: DateID Visible: No

Text Box ---     Name: txtTotalHours

NOTE: In the following sample expression, an underscore (_) at     the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.

ControlSource: =[Forms]![frmEmployees]![Dates Subform]._ [Form]![Times Subform].[Form]![txtTotTime]

Form: frmTimes ---  Caption: Times RecordSource: qryTimes Default View: Datasheet

Text Box -     Name: txtTimeIn ControlSource: TimeIn

Text Box --     Name: txtTimeOut ControlSource: TimeOut

Text Box -     Name: txtCalcTime ControlSource: =Int([CalcTime]/60) & ":" & Int([CalcTime] Mod 60)

Text Box --     Name: txtTotTime ControlSource: =Int(Sum([CalcTime])/60) & ":" & Int(Sum([CalcTime]) Mod 60) </li> Open the frmDates form in Design view, and then drag the frmTimes form from the Database window to the Detail section of the the frmDates form.</li> Set the Name property of the subform object to Times Subform, and then save and close the form.</li> Open the frmEmployees form in Design view, and then drag the frmDates form from the Database window to the Detail Section the frmEmployees form.</li> Set the Name property of the subform object to Dates Subform.</li></ol>

Testing the Example:

 * 1) Double-click the frmEmployees form.
 * 2) In the EmpName box, enter Joe User.
 * 3) In the MyDate box, enter 1/2/2001.
 * 4) In the TimeIn box, enter 09:00AM.
 * 5) In the TimeOut box, enter 12:00PM.
 * 6) Press TAB until the focus is in the TimeIn box on a new row, and then enter 01:00PM.
 * 7) In the TimeOut box, enter 06:00PM.
 * 8) On the Records menu, click Save Record. Note that the total time per row is displayed on the Times subform, and the sum of these rows is reflected on the Dates subform.

Additional query words: inf

Keywords: kbhowto KB237958

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.