Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Summing Time values in access

Posted on 2004-08-27
17
Medium Priority
?
430 Views
Last Modified: 2006-11-17
If I have a table containing 1 field and these are time values ie 10:00:00 and 20:00:00 I want to be able to sum the field and be returned with the answer 30:00:00 however when I sum the fields I get the answer 1.25. I then use the format command in the following way.

format([expr1],"hh:nn:ss")

I would expect the answer 30:00:00 but I get 06:00:00, hence it loses the 24:00:00 is there a format I can use to show time values greater than 24:00:00??
0
Comment
Question by:Carl2002
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11911756
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
0
 
LVL 4

Author Comment

by:Carl2002
ID: 11911811
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11911954
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Author Comment

by:Carl2002
ID: 11912443
Can you show me how to do this?
0
 
LVL 1

Expert Comment

by:NicholasSmith
ID: 11913237
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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11913917
More straightforwardly:

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

Expert Comment

by:GRayL
ID: 11922535
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;

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11922538
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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11922547
Been away dealing with life - but I'm all better now <G> Thanks for the welcome, glad to be back AW!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11922580
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;
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 11922644
Try my expression GRayL, you'll see it gives the same result (replacing [Expr1] with Sum([fldTime])) :)

When you sum a date/time field, you do actually get a numeric value, but Access automatically displays this as a date/time - although I missed out a ", it should read:
Format$(Fix([expr1]*24),"00") & ":" & Format$([expr1],"nn:ss")
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11922724
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.
0
 
LVL 44

Expert Comment

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

Expert Comment

by:shanesuebsahakarn
ID: 11922753
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!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11926086
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11926141
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11926263
I stand corrected, thanks:

It should be:

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question