total time

[Douglas J. Steele]

The Date/Time data type in Access is really only meant for point-in-time
(timestamps), not for durations. Date/Times are actually 8 byte floating
point numbers, where the integer part represents the date as the number of
days relative to 30 Dec, 1899, and the decimal part represents the time as a
fraction of a day. In other words, if you have 1:30, Access actually stores
0.0625 (and treats that as 01:30:00 on 30 Dec, 1899) You can do the
arithmetic, but as soon as the sum exceeds 1, Access assumes that's a day
portion, and only shows the remainder as time.

The best way is to store your durations as something else. Depending on the
resolution you want, store them as minutes or even seconds, do the
arithmetic on that, then have a custom function that will convert to how you
want to display, such as

Function FormatTimeInSeconds(TimeInSeconds As Long) As String
Dim lngDays As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long
Dim lngSecondsRemaining As Long
Dim strFormattedTime As String

' 86400 is the number of seconds in a day: 24 * 60 * 60

lngDays = TimeInSeconds \ 86400
lngSecondsRemaining = TimeInSeconds - (lngDays * 86400)
lngHours = lngSecondsRemaining \ 3600
lngSecondsRemaining = lngSecondsRemaining - (lngHours * 3600)
lngMinutes = lngSecondsRemaining \ 60
lngSeconds = lngSecondsRemaining - (lngMinutes * 60)

Select Case lngDays
Case 0
strFormattedTime = vbNullString
Case 1
strFormattedTime = "1 day "
Case Else
strFormattedTime = Format$(lngDays, "0") & "days "
End Select

FormatTimeInSeconds = strFormattedTime & Format$(lngHours, "00") & _
":" & Format$(lngMinutes, "00") & ":" & _
Format$(lngSeconds, "00")

End Function

 

[neil]

I need a query that calculates the total duration per
group. For the duration field, I am using a date/time
type. Sum doesn't work because I get a long dec value
instead of 1 hr and 30 minutes. I get 0.89888988656 for
example. I want total duration in hours and minutes in my
query.

I need your help
Thanks

when i'm done, my query should look like this:
Service Total
============================
Individual therapy 45.34
Group therapy 9.15

 

[Guest]

so, I can't use a group by clause with a function in the
select clause to get this in a query. I am using the
fields from another database. the duration field was
entered as a duration. e.g. 1:30 equals 1 hr and 30 min
in that system. so I exported it as is.

the structure of my import table is

service.....otherfields..... clientduration
Individual .... 1:30
group ..... 3:30
Individual ..... 0:30

I want to group by service using SQL and then sum the
total per service. e.g - The total time spent in
Individual should calculate 2.00

I can't do this without VBA?

 

[Michael Hopwood]

Multiply your answer by 24 to get hours.

 

[Douglas J. Steele]

No, not really. As I said, it should look as though it's working for totals
of less than 24 hours, but once you exceed a day, it won't show the values
you want. The real issue is that you're misusing the Date data type.