Solved

Summing Time values in access

Posted on 2004-08-27
17
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 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