Summing Time Fields (WXpHe+A2K)

Topic

New Reply

I have imported our time and billing logs into our Access db. From this we produce our monthly invoice to our clients, showing what we have done for them and how much we charge perfectly. From the main timelogs Tbl we have built a QryTimelogsStaff that only has 2 fields : Staff, with criteria [Select staff] and Elapsed time. This correctly displays all elaped time records for a member of staff. What I want to do is twofold: firstly to display the sum of the elaped times in another query that is based upon the QryTimelogsStaff – so far I am unable to sum(elapsedtime) get a correct answer in this, the correct answer for my newest member of staff is 57:00:00, but in spite of formatting elapsedtime to Longtime format in both Tbl and Qry I get 03:59!!

Secondly I would like to display the sum(elapsedtime) for a selected staff member with a monthly total.

(Hair tearing out for me – what is left of it) Assistance will be very much appreciated… ta!

Reply | Quote

Replies

What you may have to do is convert the time into seconds and sum that, then convert the summed seconds back to HH:MM:SS.
Pat

Reply | Quote

Is your Elapsed Time field a data/time field? I don’t see how you could be getting 59:00:00 (59 hours) if that was the case.

Reply | Quote

Hi Mark, please see the attached snapshot of the qry in question pic 1 shows the data to be summed and pic 2 the result, which is where the problem lies!

Reply | Quote

pic 2

Reply | Quote

Hi Stephen,
This may not help you, but you should know that Time fields are the same as Date fields, and they store the result in a decimal structure where the integer portion represents the number of days since 12/31/1899, and the decimal portion represents the time of day. So in order to get a sum of time fields, you have to take the number of days and multiply it by 24 when you get a sum. You should be able for format your result as a Date/Time result and make some sense of it that way, but you may need to use the Hour() and Min() functions as well as Int() in order to get a correct result.

Reply | Quote

You never did respond to my question as to whether or not your Elapsed Time is a date/time field. The reason is that if you sum these fields and try the display the result with the hh:nn:ss format, you only get the time component up to 24 hours! It converts everything above that into days. If you want to display an elapsed time of 57 hours as 57:00:00, then you will have to write a special function to return this value.

Reply | Quote

The data is imported into Long time fields, so what happens next please?

Reply | Quote

Access has just one type of field for dates *and* times: the Date/Time field. As mentioned before in this thread, the integer part of a Date/Time field represents the date or a number of days (where 0 corresponds to December 31, 1899) and the fractional part of a Date/Time field represents time (.0 = midnight, .5 = noon). The Format property determines how the value in a date field is displayed – just the date part, or just the time part, or both. There is no such thing as a Long Time field; you probably have a Date/Time field *formatted* as Long Time.

Unlike Excel, Access has no way of formatting time to display accumulated times with a number of hours above 23. You can simulate it by creating another text box.
Set the Control Source property of this text box to
=Int(24*[SumOfElapsed Time]) & Format([SumOfElapsed Time]-Int(24*[SumOfElapsed Time])/24,”:nn:ss”)
The first part of this expression computer the whole number of hours; the second part computes the remainder and formats it as minutes and seconds.

Note: this text box contains a string value; it can’t be used for futher calculations. Use the original value [SumOfElapsed Time] for calculations.

Reply | Quote

There is no such thing as “Long time fields” in Access.

Reply | Quote

Thank you Mark for pointing out my error. Of course you are right – there are no “Long Time fields” in Access. Just, in my case, “Date/Time” firlds formatted to Long Time.

Reply | Quote

Your problem is that you can’t really sum Times and then try to use the standard time format of hh:nn:ss, etc. A date field keeps track of time as a decimal amount of 1 day, so any amount more than 24 hours is a day. You will have to create a custom function to display the summed time yourself.

Reply | Quote

Not sure how your ElapsedTime data is being calculated before it is imported, or what actual data is stored in this field, so not sure if this will help. These examples use a table named tblTime with 3 fields, Staff (text), and StartTime & EndTime (Date/Time, General Date format). Elapsed Time is calculated based on the 2 date/time fields; it is not saved in table. Example of query displaying individual records:

SELECT Staff, StartTime, EndTime, GetElapsedTime([StartTime],[EndTime]) AS [Elapsed Time]
FROM tblTime
ORDER BY Staff, StartTime;

This uses GetElapsedTime function which returns a string representing Elapsed Time in hours, minutes, and seconds:

Public Function GetElapsedTime(dblStartTime As Double, _
dblEndTime As Double) As String

Dim lngElapsedTime As Long
Dim intHour As Integer
Dim intMin As Integer
Dim intSec As Integer

lngElapsedTime = DateDiff(“s”, dblStartTime, dblEndTime)
intHour = lngElapsedTime 3600 ‘Integer division operator =
intMin = (lngElapsedTime Mod 3600) 60 ‘Divide remainder by 60
intSec = (lngElapsedTime Mod 3600) Mod 60 ‘Remainder

GetElapsedTime = intHour & “:” & _
Format(intMin, “00”) & “:” & _
Format(intSec, “00”)

End Function

Example of query that sums Elapsed Time by Staff member using this function:

SELECT Staff, GetElapsedTime(Sum([StartTime]),Sum([EndTime]))
AS [Time Elapsed]
FROM tblTime
GROUP BY Staff
ORDER BY Staff;

You may be able to adapt some of this for what you are doing. Keep in mind the function returns text strings for display purposes only; if doing further calculations you need to use actual elapsed time values.

HTH

Reply | Quote