Solved

Summing Time values in access

Posted on 2004-08-27
17
417 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now