I have a call tracker database.

What I'm trying to create a query to view all my calls' time average (opened date and resolved date). Basically, how long it took to fix a call or problem... whatever...

I tried with datediff(), but had problems getting the Total Avg.

I would need the query to look like this...

Total Calls | Avg Call Time
100 | 5.33 (min.sec)


Thank you

Lucas

Create a function in a module called MinsToTime

Function MinsToTime(Mins As Integer) As String
MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")
End Function


Create a query that lists the DateDiff() between the start and end values in minutes



TotMins:DateDiff("m",Start,End)


Then create a new field in your query called

Duration:MinsToTime(TotMins)


This will give you a descriptive value for the duration

Next you will need to create another query that is based on the previous query Sum(TotMins) Average(TotMins)

This will give you what you want. To take it down to seconds then change the DateDiff("m",S,E) to DateDiff("s",S,E)


CodeMaster::cool:

Create a function in a module called MinsToTime

Function MinsToTime(Mins As Integer) As String
MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")
End Function


Create a query that lists the DateDiff() between the start and end values in minutes






Then create a new field in your query called




This will give you a descriptive value for the duration

Next you will need to create another query that is based on the previous query Sum(TotMins) Average(TotMins)

This will give you what you want. To take it down to seconds then change the DateDiff("m",S,E) to DateDiff("s",S,E)


CodeMaster::cool:



Thank you DCrake for your quick answer,

I'm just running into a problem when I add :
Duration:MinsToTime(TotMins)

I'm getting the error when running the query: Undefined function 'MinToTime' in expression.

Yes, the module has been created and I do see it under modules.


Any ideas.... :confused:

Lucas

I noticed that one time you talk of MinstoTime and another MintoTime it would be worth checking all spelling.

Brian

Also you say

Yes, the module has been created and I do see it under modules.

I hope that the module name is not the same as the function.

Brian

no luck!!

take a look

http://stpierrel.com/screenshot.JPG


I might not be doing this the right way!! :rolleyes:

It looks to me like MinstoTime is the name of a module, as I said before it cannot now be the name of a function, change the module name.

Brian

AH ok... got it....

oups... thank you very much

:o

crap... I'm not getting the right data... I thought it worked.....

Would you have this in SQL verison----

Create a query that lists the DateDiff() between the start and end values in minutes

Quote:
TotMinsDateDiff("m",Start,End)

Then create a new field in your query called
Quote:
Duration:MinsToTime(TotMins)


I would like to compare to what I have...

Here's the date I'm getting and the code

http://stpierrel.com/screenshot2.jpg

Read help on Datediff

"m" is for months
"n" is for minutes.

Brian

I see that Dcrake initially made the mistake, it wasn't a typo in this case as he repeated it but it illustrates the fact that we are all fallible and what we post should be checked if only to assist in learning.

I should have known this.

Sorry for that!!

Lucas

No problem, I didn't check DCrake's code as he ususlly gets it right.

Brian