Good day folks,

I'm hoping someone may be able to help me here. But first to give you a little background information.

The reports are based on a query, which gets it's data from a table that is linked to an external scanning system database. I have a query that extracts the desired data from this linked table into my primary working table. I than have another query that has an expression that convert the time data into the format I require [that is time format], and other activities regarding other fields, that append the data to a working table for the reporting database.

The Problem

I have created an expression in my query that converts the seconds field from the external database into a time format i.e. "hh:nn:ss". Now in my report view I get the desired results pertaining to each batch of records, but the problem lies in summing up the total time taken, because when the calculation exceeds 24 hours, for instance where the result should be 38 hours, 5 minutes and 10 seconds [38:05:10], it starts again and the result I get is 14 hours, 5 minutes and 10 seconds [14:05:10].

How can I get it to ignor the 24 hour clock, but keep the time format [hh:nn:ss] and calculate above in the way I want.

Any assistance would be most appreciated.

The expression I am using in the total field is =Sum([lngTotalTime])

John

Maybe the solution I posted here (http://www.access-programmers.co.uk/forums/showthread.php?t=114630) will help

Chris

Hi,

Thanks for your response, I will let you know how I get on.

John

Hi,

I've had a look at your solution and it works fine for integer type data, but my data is in actual time format "hh:nn:ss"!

So for instance I have in my total time field [lngTotalTime] the following for each record:

00:14:24
01:02:47
00:34:44
00:46:36

and so on......

Now once the total of these fields reaches 24 hours 24:00:00, instead of increasing the total to the next hour which should be 25 hours i.e. 25:14:38 for example it starts back at 00:14:38, which is what I don't want to do.

So what I need to do is to be able to get the time format to ignore the conventional maximum of 24 hours i.e. 24:00:00 and when it reaches 24 hours to increase the total value in the collection of records as follows:

From----To
24:00:00
Add------Result
01:42:36-25:42:36
Add------Result
01:20:22-27:02:58
Add------Result
00:20:33-27:23:31

To total up to 27:23:31, 27 hours, 23 minutes and 31 seconds

To use your method in your link would mean I would have to convert my timings fields to integer's, which would mess up a lot of the other aspects of the database, which I would not want to do.

Your assistance would be most appreciated.

John

To use your method in your link would mean I would have to convert my timings fields to integer's, which would mess up a lot of the other aspects of the database, which I would not want to do. No that is not the case. The formula I gave is intended to be used on reports and forms. It does not require any changes to your underlying data structure assuming you are using the date datatype. It is designed to display elapse time i.e. not constrained to 24hr clock.
hth
Chris

Hi Chris,

I've had no success in getting what I need and I'm somewhat lost as to what I need to do to achieve the desired results.

I've attached a sample.mdb with the undelaying tables, queries and reports. Perhaps you could have a look and maybe you might be able to workout what I need to do.

If you look at the report named "rptFFStatsOperatorVolTime" in preview view and go to page 6 you will see that the total time is shown as 18:27:35 when in fact it should be 36:15:35.

Now in terms of records that are less than 24 hours, the calculations work fine, it's when the times go over that threashold that I need to address.

The same problem occurs in the rptFormFamilyStats report.

Your assistance would be most appreciated.

John

P.S. Sorry for some reason the system won't let me upload the zip file I created or the stright mdb file!! I will try again in a short while

Hi Chris,

Managed to get it uploaded this time, please see attached.

Your assistance would be most appreciated.

John

Hi -

The problem is that you are attempting to depict/store elapsed time (a number of hours, minutes, seconds) as a time as seen on a clock.

See this MSKB Article which describes how Access stores dates/times. http://support.microsoft.com/kb/q130514/

Don't know how you're arriving at the 'elapsed' time. However, when I convert the total of these times for operator BILLIND to total seconds =sum(cdbl([yourElapsedTime])) * 86400 (number of seconds in a day), I arrive at 239255, which converts to 2 days 18 hours 27 minutes 35 seconds

Public Function TimeFHNS2(totSec As Long) As String
'Input: ? TimeFHNS(239255)
'Output: 2 days 18 hours 27 minutes 35 seconds

Dim z As Double

z = totSec
'capture days and hours
i = z \ 86400 & " day" & IIf(z \ 86400 <> 1, "s ", " ") & (z Mod 86400) \ 3600 & " hour" & IIf((z Mod 86400) \ 3600 <> 1, "s ", " ")
'capture minutes
i = i & ((z Mod 86400) Mod 3600) \ 60 & " minute" & IIf(((z Mod 86400) Mod 3600) \ 60 <> 1, "s ", " ")
'capture seconds
TimeFHNS2 = i & ((z Mod 86400) Mod 3600) Mod 60 & " second" & IIf(((z Mod 86400) Mod 3600) Mod 60 <> 1, "s", "")

End Function

Don't know if that helps or further confuses the issue. Hopefully the cited article will be of assistance.

Bob

Hi Bob,

Thanks for your help, however I have worked out a way to acheive what I need, albeit a perhaps not as efficient as it could be.

I created a number of unbound text boxes and named them as follows:

txtTotalSecsMod with this expression in the control source "=Format(Sum(Right([dtmTotalTime],2)) Mod 60,"00")"

txtTotalSecs with this expression in the control source "=Sum(Right([dtmTotalTime],2))\60"

txtTotalMinsMod with this expression in the control source "=[txtTotalMins] Mod 60"

txtTotalMins with this expression in the contron source "=Sum(Mid([dtmTotalTime],4,2))+[txtTotalSecs]"

txtTotalHrs with this expression in the control source "=Format([txtTotalMins]\(60),"00")+[txtTotalHours] & ":" & Format([txtTotalMinsMod],"00") & ":" & Format([txtTotalSecsMod],"00")"

I replaced my txtTotalTime with the txtTotalHrs text box

This now gives me the desired look and results. Whilst I appreciated it may not be the best solution, it at least gives me what I need for now, until I can come up with a better solution.

Once again thanks to all who responded to my question which helped me get what I wanted.

John