Summing Seconds Fields 2

[Bennie47250]

Using Access 2002

We have a home grown help desk package and it has a start time field and an end time field. These fields are defined as Date/Time fields.

I’m using the DateDiff function to determine the length of time in seconds that the call was open. (DateDiff("s",[Calls IR]![StartDate],[Calls IR]![EndDate])

Now I would like to take all the calls for each agent and add them up for a day or a week. The formula I’m using today does not do this correctly.

Here is an example of the data we have right now

Call 1 = 37 seconds
Call 2 = 826 seconds (13 min 46 seconds)
Call 3 = 2452 seconds (40 min 52 seconds)
Call 3 = 44 seconds

This adds up to be 3359 seconds. I would like to express this in Hours Minutes and Seconds. In this example the total elapsed time is 55 minutes and 59 seconds.

How can this be accomplished?

Thanks
Bennie

 

[PaulBricker]

Try this expression.

Int([TotalSeconds]/3600) & " Hour(s), " & Int(([TotalSeconds]/ Mod 3600)/60) & " Minutes, " & ([TotalSeconds]/ Mod 3600) Mod 60 & " Seconds"

You can up it in a query where you add up the total or you can put it in a Function.

Paul

 

[Bennie47250]

Paul, I tried this expression in a text box in the report footer and it displays 01:56:59 for the total time when the report runs.

Any thought as to why this is not showing 00:55:59?

Thanks
Bennie

 

[dhookom]

Using the expression from PaulBricker, how can you suggest the result includes ":"s. I don't see any ":" in his calculation. Paul's formula uses Int() which will round down.

Duane
MS Access MVP

 

[PaulBricker]

Bennie, I started off with your total seconds example of 3359 seconds and the results were 0:55:59. I then went on to try a dozed other examples before I posted it. I did not test it out as the Control Source for a textbox and suspect that's where the problem is. If your report is based on a query then you could put the expression in a new column of your query
MyTotals:Int([TotalSeconds]/3600) & ":" & Int(([TotalSeconds]/ Mod 3600)/60) & ":" & ([TotalSeconds]/ Mod 3600) Mod 60

and then reference the field MyTotals directly in your Report. If I was doing it that's what I would do.
How are you arriving at your TotalSeconds value. Are you summing them in a query or adding them up directly in the Report?

Paul

 

[Bennie47250]

I may not be dong the the "best" way, and I'm open to suggestions on how to make it better.

I have a query that is reading the tables. In this query I'm doing the DateDiff function to determine the number of seconds.

The report is based off the query. In the report detail section, I would like to be able to take the number of seconds and show that this call was open for 5 minutes 13 seconds or 00:05:13 (or something like this rather than saying call open 331 seconds.

Then in the group footer and report footer, I would like to show the total time that the agent logged for the day or week. Again I can sum up the total # of seconds just want to display them in the hour, minute, seconds format rather than showing it as 18752 seconds.

Thanks

 

[dhookom]

Try set your control source to something like:

=Sum([TotalSeconds])\3600 & ":" & Format((Sum([TotalSeconds]) Mod 3600)\60,"00&quot & ":" & Format(Sum([TotalSeconds]) Mod 60 ,"00&quot


Duane
MS Access MVP

 

[PaulBricker]

That did it for the sample data I just worked up Duane. I like the addtion of the Format function as well. Nice.

Paul

 

[PaulBricker]

Something I just noticed (which may explain your original error), in my first expression I somehow got a / (dividend sign) in the expression after the field [TotalSeconds]. That isn't supposed to be there. I must have messed something when copying it. Duane's expression works and is a little more succinct. Just to clarify his changes, he gets rid of the Int() function and uses the Integer Dividend sign \ instead. It does the same thing as the Integer function with the added benefit of keeping things easier to read. If you just change your seconds field name for [TotalSeconds] in his expression, that should do it for you.

Paul

 

[dhookom]

Thanks for the further clarification on the "\". I should have mentioned that.

Duane
MS Access MVP