Hello,
I am facing the following problem....I am an access newbie:
I have to make a form which has the following controls - DayHours,
NightHours, TotalHours.
Day shifts are defined as shifts from 7am until 6pm
Night shifts are defined as shifts from 6pm until 7am
I have a table with EmployeeID, StartTime, EndTime.
As an example, if exmployee X worked from 1pm until 10pm - that should
count as 6 day work hours and 3 night work hours.
How could I implemet such a system that works out the day and night
work hours based on the shift times???
Many thanks in advance,
Nadav
Your question is a multipost (posting the same question independently to
multiple newsgroups). However, because you are a self-proclaimed newbie
I'll chalk that up to ignorance -- this time.
The only reasonable way to handle work shifts is to include both date
and time information for every StartTime and EndTime. You can do that
by using a Date/Time field for both StartTime and Endtime. If you need
to know the start and stop time for the shifts on a given day you can
use the DateValue and TimeSerial functions:
DateValue(StartTime) to get the date of the StartTime and TimeSerial(7,
0, 0) to get the start time of the day shift.
Then you need to put those together. To get around using knowledge of
the specific data type used to store the Date/Time field in Access I
might do something like the following:
'One way to get a Date/Time value into seconds is to put the following
function in a Module:
Public Function DDHHNNSS2Seconds(strTime As String) As Long
Dim lngDays As Integer
Dim intHours As Integer
Dim intMinutes As Integer
Dim intSeconds As Integer
Dim intColon As Integer
intColon = InStr(1, strTime, ":", vbTextCompare)
lngDays = CLng(Left(strTime, intColon - 1))
intHours = CInt(Mid(strTime, intColon + 1, 2))
intMinutes = CInt(Mid(strTime, intColon + 4, 2))
intSeconds = CInt(Right(strTime, 2))
DDHHNNSS2Seconds = intSeconds + 60 * CLng(intMinutes + 60 *
CLng(intHours + 24 * lngDays))
End Function
'Example:
'DDHHNNSS2Seconds(Format(#6/15/2008 7:00:00 AM#, "\0\0\:hh:nn:ss")) => 25200
'More module code:
'At the top of a module before subs and functions:
Public Const DAYSHIFTSTART = "00:07:00:00"
Public Const NIGHTSHIFTSTART = "00:18:00:00"
'Also in a module:
Public Sub DayAndNightHours(StartTime As Date, EndTime As Date, ByRef
dblDayHours As Double, ByRef dblNightHours As Double) As Boolean
Dim dblDayShiftStartSeconds As Double
Dim dtDayShiftStart As Date
Dim dtDayShiftEnd As Date
Dim dtNightShiftStart As Date
Dim dtNightShiftEnd As Date
Dim dblDayShiftHoursAvailable As Double
Dim dblNightShiftHoursAvailable As Double
DayAndNightHours = False
dblDayShiftStartSeconds = DDHHNNSS2Seconds(DAYSHIFTSTART)
dtDayShiftStart = DateAdd("s", dblDayShiftStartSeconds,
DateValue(StartTime))
dtDayShiftEnd = DateAdd("s", DDHHNNSS2Seconds(NIGHTSHIFTSTART),
DateValue(StartTime))
dtNightShiftStart = dtDayShiftEnd
dtNightShiftEnd = DateAdd("d", 1, dtDayShiftStart)
dblDayShiftHoursAvailable = DateDiff("s", dtDayShiftStart,
dtDayShiftEnd) / 3600#
dblDayHours = DateDiff("s", dtDayShiftStart, EndTime) / 3600#
If dblDayHours >= dblDateShiftHoursAvailable Then
dblDayHours = dblDayShiftHoursAvailable
End If
dtNightShiftStart = dtDayShiftEnd
dblNightShiftHoursAvailable = Datediff("s", dtNightShiftStart,
dtNightShiftEnd) / 3600#
dblNightHours = DateDiff("s", dtNightShiftStart, EndTime) / 3600#
If dblNightHours >= dblNightShiftHoursAvailable Then
dblNightHours = dblNightShiftHoursAvailable
End If
DayAndNightHours = True
End Function
Code behind Form Example:
Private Sub Test_Click()
Dim dblDayHours As Double
Dim dblNightHours As Double
dblDayHours = 0
dblNightHours = 0
Call DayAndNightHours(#6/15/2008 7:00:00 AM#, #6/16/2008 2:15:00 AM#,
dblDayHours, dblNightHours)
MsgBox ("dblDayHours = " & dblDayHours)
MsgBox ("dblNightHours = " & dblNightHours)
End Sub
'Output:
dblDayHours = 11
dblNightHours = 8.25
That should give you a starting point. Be sure to test the code
thoroughly if you plan on using it. All kinds of crazy situations come
up with time tickets and not including both the date and time
information for the StartTime and EndTime is just asking for trouble
later. The functions might need further adjustment if their values are
to show up in a query. However, using VBA might be more flexible than
using a query because of the volatile nature of what time tickets are
required to do. The day part of the time string is left over from some
other use of the function but who knows if it might be prove useful
later
? Note that the code limits the day and night hours to the
hours available in the shift. It does not deal with, say, someone
starting during the night shift and working into the day shift of the
following day. I can think of other, easier ways to convert the time to
seconds without resorting to manipulating the floating point
representation used for dates in Access, but the DDHHNNSS2Seconds
function I posted is intuitive enough and easy enough for now.
James A. Fortune
(e-mail address removed)
