[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Summing Time values in access

Posted on 2004-08-27
17
Medium Priority
?
428 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 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