Format Average of Minutes

[doulostheou]

I have a query that finds the length of each call Length: DateDiff("n",[TimeIn],[TimeOut]). This works fine.

However, when I try to find the average Call length for each type of call it gives me the correct answer but the formating could lead someone to believe it was incorrect. For instance, my average Auto claim take 17.56 minutes. How can I make this display a time value instead of a Fixed number?

[This message has been edited by doulostheou (edited 04-27-2002).]

 

[Jack Cowley]

Can't you put a lable just to the right of the field that say, "Minutes"? How would you display this any differently than 17 minutes and a bit over 30 seconds?

 

[doulostheou]

This is what I've done. I'd just rather see minutes and seconds as it seems less confusing. I know what it means, but not all the users are too quick. Some will see 5.25 and think it is 5 minutes and 25 seconds. Others will see 5.75 and send me an e-mail asking why it didn't display 6 minutes and 15 seconds. Training can take care of this, but if I could get it back into a time format (5:45), it would be better.

I didn't know if there was a format function that would take care of this.

[This message has been edited by doulostheou (edited 04-27-2002).]

 

[Jack Cowley]

I think you will need a Select Case statement to change the fraction to seconds.

Select Case Fraction
Case .1
Seconds = 6
Case .2
Seconds = 12
et cetera

I think you get the idea....

 

[raskew]

Here's a sample table:

CallID CallType startTime endTime
1 1 4/2/02 6:27:52 PM 4/2/02 7:05:32 PM
2 1 4/2/02 7:05:46 PM 4/2/02 7:41:06 PM
3 1 4/3/02 4:29:43 AM 4/3/02 5:35:00 AM
4 2 4/3/02 7:03:29 PM 4/3/02 7:30:55 PM
5 2 4/3/02 7:31:09 PM 4/3/02 8:00:41 PM

Here's the query SQL:

SELECT tblCallTimes.CallType, Int(Avg(DateDiff("s",[starttime],[endtime]))/60) & ":" & Format(Avg(DateDiff("s",[starttime],[endtime])) Mod 60,"00") AS Expr1
FROM tblCallTimes
GROUP BY tblCallTimes.CallType;

Here's the output:

CallType Expr1
1 46:06
2 28:29