You can't use a date/time data type for this as that represents a point in
time rather than a duration. It is actually implemented in Access as a 64
bit floating point number as an offset from 30 December 1899 00:00:00 with
the integer part representing days and the fractional part the times of day.
You could use a single column of text data type and parse out the days,
hours and minutes values, but the simplest approach is to use three separate
columns (fields) for Days, Hours and Minutes. You can give the latter two
validation rules of <24 and <60 respectively.
To sum the values of the three fields and return a single string value in
the format days:hours:minutes I'd suggest adding a little function to a
module in your database:
Public Function DHM(intDays As Integer, intHours As Integer, intMinutes As
Integer) As String
Dim intMinVal As Integer
Dim intHourVal As Integer
Dim intDayVal As Integer
intHourVal = (intMinutes \ 60) + intHours
intDayVal = (intHourVal \ 24) + intDays
intHourVal = intHourVal Mod 24
intMinVal = intMinutes Mod 60
DHM = intDayVal & ":" & intHourVal & ":" & intMinVal
End Function
This uses integer division to get the number of days in the total number of
hours and the number of hours in the total number of minutes, and the Mod
operator to return the number of hours and minutes left over. You can call
it as the ControlSource property of a text box in the report footer, passing
the sum of each column into the function like so:
=DHM(Sum([Days]), Sum([Hours]), Sum([Minutes]))
I'd give some thought to the desirability of representing job times in this
way, however. Because people by and large don't tend to work 24 hours in a
day, a day as a unit for payroll or invoicing purposes is in most situations
not very suitable (there are exceptions: Jim Rockford seemed to go on for
years charging "200 dollars a day plus expenses", and I used to be paid at a
daily rate myself in the days when I did a lot of site work). The majority
of time logging applications I've encountered have worked in hours, however.
Ken Sheridan
Stafford, England
KGB in Toronto said:
I have a table which collects the time it took for a service call to be
completed. Currently i can only enter up to 23:59 minutes. But however some
service calls takes days to resolve the problem(s). How do I modify this to
include days:hours:minutes? Also this data (field) is summed up at the end of
the report. currently it works out ok giving me eg say 80:25 to mean 80hours
and 20minutes. I would like the hours to be converted to days. Any help will
be greatly appreciated.
