Employee Time Sheet Question

[Ginzell]

I'm doing a Time In /Time out / Total for Employee's and I'm using the
function below, but I'm not getting the right totals back.
I'm getting:
Time In Time Out Hours:Minutes
7:00:00PM 9:00:00AM -10
---
Total Hours = -10
If I use regular hours - say, 9-5 it works, but when I get into graveyard
shifts it messes up. Any help greatly appreciated.

Option Compare Database
Option Explicit

Public Function HoursAndMinutes(interval As Variant) As String

Dim totalhours As Long, totalminutes As Long, totalseconds As Long
Dim hours As Long, Minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

totalhours = Int(CSng(interval * 24))
hours = totalhours Mod 24

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
Minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then Minutes = Minutes + 1 ' round up the minutes and
If Minutes > 59 Then hours = hours + 1: Minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(Minutes, "00")


End Function

 

[David C. Holley]

Capture the full date/time of the time in/time out and then use the
DateDiff() to perform the calculation. The examples below will return
the total number of MINUTES the person worked. This will also have some
benefits in that you'll be able to tie a specific shift back to a date
or pay period and other whatnot*.

David H
*my new favorite word

?DateDiff("n", #8/30/2005 1:07:00PM#, #8/30/2005 10:00:00 PM#)


?DateDiff("n", #8/30/2005 11:07:00PM#, #8/31/2005 8:00:00 AM#)

 

[Ginzell]

I'm sorry, I'm a little lost on this still.
Do I put this in my function? If so where?
And if not, where do I put it? I'm just not sure what you mean
by capture the full date/time
Thanks

 

[David C. Holley]

I'll elaborate. The problem with the calculation is that by NOT
capturing the Date as in #8/30/2005 1:07:00 PM# as opposed to #1:07:00
PM# is that you do not have a built in point of reference for the
calculation. As such you're having to bend over backwards to determine
if the TIME OUT ocurred after midnight and then adjust things
acccordingly. Basically, Access stores Dates & Times as numbers. For
example, 9/1/2005 8:50:12 AM is stored as 38596.3681944444 where the
DATE is represented by the 38596 and the decimal is the time. The 38596
is the number of days since #12/31/1899# or #1/1/1900# (can never
remember). When you capture the full date/time, its alot easier to
determine if the shift crossed midnight or not. Among other things, by
just capturing the time, you introduce the possibility that the person
may have entered the wrong value.

Change the manner in which the person enters the TIME IN and TIME OUT
to include the DATE. You might be able to do something where the user
enters the time and then you programically attach TODAY'S DATE. If the
TIME OUT is less than TIME IN then you subtract 1 from TODAY's Date. I
would go with a situation where you have two bound fields for TIME IN
and TIME OUT that display the full Date/Time. This fields would be
locked. I would use two UNBOUND fields to capture the TIME IN and TIME
OUT and then use code to set the values of the BOUND controls. The code
would execute on the AfterUpdate event of the unbound controls. You may
also want to add a warning/confirmation message when the value of TIME
OUT is earlier than TIME IN to confirm that the shift spanned midnight.
Of course, I would also only accept MILITARY time via an appropriate
input mask. (Its easier to enter 17:00 as opposed to 5:00 PM).

Side note, if the DB already contains records, you'll probably want to
do some work to update the TIME IN and TIME OUT to add the DATE to those
preexisting values. This can be done via QUERIES or DAO. ***MAKE A
BACKUP OF THE TABLE BEFORE TRYING THIS***

 

[Ginzell]

Ok, I'm understanding better now, I just can't picture how I programically
attach the date, and, what code would I use to update the bound controls?
The program will have to have the date of the day of work, not today's day.
It's a schedule for days working for the next two weeks. Will I still need
the function that I have?
I appreciate your help so much, I've been searching for this for days now.

 

[David C. Holley]

One way would be to use the CDate() function to create the full dates as in

?CDate(Date() + #11:17:42 AM#)
?CDate(Date() + Me.TimeIn)
?CDate(CDbl(Date()-1) + #11:17:42 AM#)

Updating the bound controls is as simple as having code fire on the
AfterUpdate event for each. (Select [Event Procedure] for the
AfterUpdate event in the property sheet). Access will automatically add
the following lines of code to the form's module...

sub timeIn_AfterUpate()
end sub

From there add your code. The most fundamental code would be

Me.controlA = Me.timeIn

Your function will look something like...

Me.fullTimeIn = CDate(Date() + Me.timeIn)

Since I don't know what's going on with the SHIFT DATE, I can't add that
to the function. But basically, that value would go into the CDate()
function replacing the Date() as in

Me.fullTimeIn = CDate(Me.shiftDate + Me.timeIn)