Time calculations over 24 hours

[Guest]

I have developed an Access db(my first proj in Access) that tracks CPU usage
times. I have a spreadsheet of jobs with CPU time and actual processing
times for each person in my group. I need to keep track of total times use
for each individual by month, year and total for entire department. I import
my time fields into a LONG TIME field. I have reports showing totals but
they are not accurate. I have an Excel ss that shows the time correctly. My
current problem is trying to add 18:18:17 + 7:34:21 = 25:52:38, it shows
correctly in Excel but in Access it shows 1:52:38. Is there a way to
calculate total time with values exceeding 24 hours?

 

[Douglas J. Steele]

There's no such thing as a "Long Time" field in Access. The only date/time
related data type is the Date type, which really is only intended to be used
for timestamps (i.e.: specific date/time combinations), not durations. This
is because under the covers, the Date data type is a 32 byte floating point
field, 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.

The best way to handle durations in Access is to store them as Long Integers
(where 1 represents whatever granularity you require: 1 second, 1 minute, 1
hour, etc.). You can easily write functions to convert from the long integer
value into hh:nn:ss format and vice versa.

However, if you want a bit of a kludge, see my October, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html