Access really doesn't have a time data type: the date/time field is intended
to store timestamps (the field is an 8 byte floating point number, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day)
What this means is that 8:00:00 would represented as 0.3333 (a third of a
day), 6:00:00 would be 0.25 (a quarter of a day) and 12:00:00 would be .5.
If you add them together, you'd expect 26:00:00, but since 0.3333 + .25 + .5
is 1.0833. Access sees that as 1 day, and 2 hours.
Realistically, you have 2 options. One is to use some other data type to
store the hours. For example, if you want to be able to go done to minutes,
store your times as minutes, and write a function that can convert total
minutes to hh:mm format:
Function FormatTotalMinutes(TimeInMinutes As Long) As String
Dim lngHours As Long
Dim lngMinutes As Long
lngHours = TimeInMinutes / 60
lngMinutes = TimeInMinutes - lngHours
FormatTotalMinutes = Format$(lngHours, "0") & _
":" & Format$(lngMinutes, "00")
End Function
The other is to convert the 1.0833 to 26:00:00 using something like:
FormatDateToTime(TotalTime As Date) As String
Dim lngHours As Long
Dim lngMinutes As Long
lngHours = DatePart("d", TotalTime) * 24 + _
DatePart("h", TotalTime)
lngMinutes = DatePart("n", TotalTime)
FormatTotalMinutes = Format$(lngHours, "0") & _
":" & Format$(lngMinutes, "00")
End Function
(Warning: the sample code above is untested)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
CD Tom said:
I've got a payroll program that we use that the employees clock in and out
on. The system keeps track of the daily times, the problem I'm having is
when I go to add up the times for the week the report will only add up to 24
hours and then start over. I know it has something to do with the time and
that a day only has 24 hours. Is there any way to ignore the 24 hour
restriction on the time and make it add up to 40 or more hours. Thanks for
any help I really need it.
