Summing Time values in access

the problem here is that Date values in Access are actually stored and handled as NUMBERS of type double ( a value with a decimal point).  The INTEGER part of the number (to the left of the decimal point) is the count of the number of days since Dec 30, 1899, while the fractional part (the value to the right of the decimal point), is the time (measured in seconds since midnight), as a fraction of 1 day (1 day = 86400 seconds).

Thus EVERY time value is measuerd in Days and fractions of a day - thus 30 hours = 1 day + 6 hours or 1.25 days (which is exactly the value that you are getting)

You can convert that 1.25 to total hours, minutes and seconds by converting the Date variable to a Type Double, and then multiplying by 24 (# of hours per day), and then, in code, converting the reulting NUMBER back to the equivalent Hours, minutes and seconds.

AW

Arthur

Isn't this what I've tried to do by using the line:

Format([expr1],"hh:nn:ss") ??

But although the number is 1.25 I still get the answer 06:00:00.

Carl.

no, because expr1 is internally 1.25  and you ar then only showing the Hours, minutes and seconds from the time part of that result (.25 = 6:00:00)  when a Data is passed to the format function that does NOT include in the format specification anything to handle the INTEGER part of the value ("DD:HH:nn:ss") then the integer part is ignored.

The format function does not convert 1 day to 24 hours and add that to the hours part of the time.

AW

Can you show me how to do this?

your on the right lines. what i would do is to:

convert the answer to a string - thus you get "6:00:00"
take the first number before the ":" val(left(cstr([Time]),instr(1,cstr([Time]),":"))) - gives you 6
take the integer of the time, so 1.25 would give you 1, then multiply this by 24 and add to the 6 giving 30.

a bit long winded and if anybody comes up with a better solution then i'm all ears. heres the solution in full:

CStr(Val(Left(CStr(Format([Time],"hh:nn:ss")),InStr(1,CStr(Format([Time],"hh:nn:ss")),":")-1))+(Int([Time])*24)) & Mid(CStr(Format([Time],"hh:nn:ss")),InStr(1,CStr(Format([Time],"hh:nn:ss")),":"))

Hope this helps

More straightforwardly:

Format$(Fix([expr1]*24),"00") & :" & Format$([expr1],"nn:ss")

I believe the expression will be as the result of a time of times.
Select sum(fldTime) as SumofTimes from TimesTable;

However, if the sum exceeds 24 hours you will get a date time string.

Select Sum(fldTime ) * 1 as SumofTimes from TimesTable; This will yield days and fractions of days.

Working down the pieces:

Select Int(Sum(fldTime ) * 1) as Days, CDate(Sum(fldTime) * 1 - Days)) as Frac,
Days * 24 + format(Frac,"hh")) as Hours, (CStr(Hours) & format$(Frac,":nn:ss")) as DHMS from Table;

yo, shane---where have you been hiding lo these many months?  It's good to see you back on the boards.  Thought we had lost you.

AW

Been away dealing with life - but I'm all better now <G> Thanks for the welcome, glad to be back AW!

Sorry a few paranthetical problems. Try this:

Select Int(Sum(fldTime) * 1) as Days, CDate(Sum(fldTime) * 1 - Days) as Frac,
Days * 24 + format(Frac,"hh") as Hours, (CStr(Hours) & format$(Frac,":nn:ss")) as DHMS from dailyappts;

However, as said above by shanesuebsahakarn, more straighforwardly (after fixing his very elegant answer), the following query using his formula does it:

Select Format$(Fix([fldTime]*24),"00")  & Format$([fldTime],":nn:ss") from TimesTable;

Originally & :" didn't cut it without the other double quote. I also found that by leading the nn:ss string with a colon produce the correct result.

He deserves the points.

Sorry, I was typing when you responded. Note that the leading colon in ":nn:ss" allows just one concatination. Very clever your formula!

Thanks GRayL :) You're right about the : in the Format statement, I should have figured that myself, given that I was going for the shortest expression possible!

Sorry, I just noticed another omission:

Select Format$(Fix([fldTime]*24),"00")  & Format$([fldTime],":nn:ss") from TimesTable;

Should be:

Select Format$(Fix(Sum([fldTime]))*24,"00")  & Format$(Sum([fldTime]),":nn:ss") from TimesTable;

First you want the sum, and then you want to multiply the integer part by 24, not the whole sum.

No, you do actually want to multiply the sum by 24 and retrieve the integer of the multiplication. The reason is this:

If the Sum comes to 1.5 (1 and a half days), you want to return 36.
Fix(1.5)*24=24
Fix(1.5*24)=36

I stand corrected, thanks:

It should be:

Select Format$(Fix(Sum([fldTime]) * 24),"00")  & Format$(Sum([fldTime]),":nn:ss") from TimesTable;