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.

Thank you very much. I'll try it and let you know how it goes.
cheers

"Ken Sheridan" wrote:

> 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" wrote:
>
> > 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.
>

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" wrote:

> 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.

On Mar 28, 1:58 am, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:
> the simplest approach is to use three separate
> columns (fields) for Days, Hours and Minutes. You can give the latter twovalidationrules of <24 and <60 respectively.

If this a scalar value -- and I suggest that it is -- then it should
be in one column. I'd recommend using the smallest time granule
required (i.e. minutes) as the scale.

Jamie.

--