Average of Time in Minutes:Seconds

[CanadianAccessUser]

Hello All,

Basically, I get daily reports from the client and the AHT and ACW values come in seconds. I've recently added code to the query changing the data to nn:ss which works perfectly. Below is some info:

tblTelephony
[AgentName]
[Calls] (number of calls answered)
[AHT] (in seconds)
[ACW] (in seconds)

qryTelephonyMinSec
[AgentName]
[Calls]
[AHTMinSec] = AHTMinSec: Format(Int(([AverageHandlingTime]-(Int([AverageHandlingTime]/3600)*3600))/60),"00") & ":" & Format((([AverageHandlingTime] Mod 60)),"00")
[ACWMinSec] = AfterCallWorkMinSec: Format(Int(([AfterCallWork]-(Int([AfterCallWork]/3600)*3600))/60),"00") & ":" & Format((([AfterCallWork] Mod 60)),"00")

The above code works very well, where I'm stuck is in the attempt to average the fields [AHTMinSec] & [ACWMinSec] by Agent in a report.

Using [AHTMinSec] as an example:
I have the daily values for each agent [AHTMinSec] in the detail, Avg([AHTMinSec]) in the Agent group footer showing each agent's average, Avg([AHTMinSec]) in the Team group footer showing team averages and Avg([AHTMinSec]) in the report footer showing the campaign average including all teams. When I run the report the details are hidden providing each agent's average, the team average, and the campaign average.

The report worked just fine until I converted to minutes with the above code. Is there a reason that I'm getting an error stating that the calculation is too complex? I've done enough research to determine that the db can't Sum in order to average... HELP!

 

[plog]

Average only works on numbers, AHTMinSec is a string. You need to work in numbers until its time to show your data. My suggestion is to take your calculation that converts seconds into your minutes string and make it a function in a module like so:

Function getMinuteFormat(in_Seconds) As String
 ' converts numeric seconds (in_Seconds) into a string that displays minutes (i.e. 340 = "5:40")

Dim ret as string          ' variable to hold value to be returned, by default
ret="0:00"                  ' default value to return
 
' put your formatting code here


getMinuteFormat = ret

End Function

Then to call it you use this in a query:

TotalMinutes: getMinuteFormat(Avg([AHT])

 

[CanadianAccessUser]

THANK YOU SO MUCH!!
I've got it working except for the formatting code I was supposed to enter... How do I enter it into a module if it has specific field names in it and I want to use it for multiple fields? Do you have an example I could try?

 

[plog]

in_Seconds represents the number you pass it. So, the only variable you use is in_Seconds, you don't use any of your field names, you use in_Seconds because it contains the value.

 

[CanadianAccessUser]

Is this what I'm looking for?
Format(Int(((in_Seconds) - (Int((in_Seconds) / 3600) * 3600)) / 60), "00") & ":" & Format((((in_Seconds) Mod 60)), "00")
I tried it and I get an error saying that the first "&" is expected to be an "="...
I'm sure I seem like I know what I'm doing, but it's mostly self taught so please bare with me...

Just for the sake of trial and error I also tried this but got the same error:
Format(Int(([in_Seconds] - (Int([in_Seconds] / 3600) * 3600)) / 60), "00") & ":" & Format((([in_Seconds] Mod 60)), "00")
and
Format(Int((in_Seconds - (Int(in_Seconds / 3600) * 3600)) / 60), "00") & ":" & Format(((in_Seconds Mod 60)), "00")

 

[plog]

When you are working in a Module you no longer have to cram everything into one statement. You're parenthesis are hurting my brain, instead of using so many, just make each one a new line in the module--break that thing up into digestable parts. Also, you need to set the variable 'ret' to the value so start with this:

Function getMinuteFormat(in_Seconds) As String
 ' converts numeric seconds (in_Seconds) into a string that displays minutes (i.e. 340 = "5:40")

Dim ret As string          ' variable to hold value to be returned, by default
Dim mins, secs As Integer	' variables to hold minutes and seconds

ret="0:00"                  ' default value to return

mins = in_Seconds/60		
secs = in_Seconds Mod 60 
 ' determines how many whole minutes are in in_Seconds, and how many remaining seconds

ret = mins & ":" & secs


getMinuteFormat = ret

End Function

 

[CanadianAccessUser]

Sorry I hurt your brain... hahaha
we're getting close tho!

The values my report is giving me look like this:
5.1090154102776:7

min = in_Seconds / 60 is great but I need it to omit everything between the decimal and the colon since it is part of a minute and therefore taken care of with secs = in_Seconds Mod 60.
How would you recommend I move forward?

PS. you're a superstar! This help is awesome

 

[plog]

I recommend you play around with it. Did you build that expression that you currently have? You have 2 functions (Format() and Int()) in there that you should be able to use in getMinuteFormat to produce output exactly as you desire.

Here's a link to a handy reference guide for Access functions: http://www.techonthenet.com/access/functions/

 

[CanadianAccessUser]

No I didn't build it, I totally stole it from another thread... I'll keep playing tho.
I added:
min = Int(in_Seconds / 60)
and I'm getting a full minute now.

At this point all I have to do is figure out how to make 5:3 show as 5:30 or 5:03 and I'm all set

 

[plog]

Good. Here's a hint--use an IF. Less than 10, give it a "0".

 

[CanadianAccessUser]

I have tried a few things including:
secs = IIf(in_Seconds Mod 60 < 10, Format(in_Seconds Mod 60, "00"), in_Seconds Mod 60)
and it's not making a difference... what am I missing?

 

[plog]

First, once you have mins and secs set, you no longer need in_Seconds. Second, you shouldn't need to change the values of mins and secs one they are set--use ret. That is going to be the value returned and it is going to be a string. Third, Format isn't necessary at all. Check to see if secs are less than 10, if so add a "0" to ret.

If secs < 10 Then ret = ret & "0"

 

[CanadianAccessUser]

It's ALIVE!!

Function getMinuteFormat(in_Seconds) As String
' converts numeric seconds (in_Seconds) into a string that displays minutes (i.e. 340 = "5:40")
Dim ret As String ' variable to hold value to be returned, by default
Dim mins, secs As Integer ' variables to hold minutes and seconds
ret = "00:00" ' default value to return
mins = Int(in_Seconds / 60)
secs = in_Seconds Mod 60
' determines how many whole minutes are in in_Seconds, and how many remaining seconds
ret = mins & ":" & Format(secs, "00")

getMinuteFormat = ret
End Function

works perfectly
Thank you for your help plog!