Calculating Average for a field which is calculated field itself

[Moor]

Hi,

Earlier I have requested for help on calculating the time difference in
dd:hh:mm format which is day:hour:minute...as below:

==============
Hi,
I am a beginner to MSAccess. Recently I imported a huge file contains
data about Telco providers outage timings as follows:
Following are the three columns from excel.
"Problem Start Time ", "Problem End Time" and "Duration", duration is
the difference between the first two columns.

It was all ok when these fields are at excel as I have to display them
in d:hh:mm format which is days:hours:minutes.
Pls. take a look at the exact format below.

Start Time End Time Duration
m/d/yyyy h:mm - m/d/yyyy h:mm = d:hh:mm
5/23/2006 16:19 - 5/25/2006 11:21 = 1:19:02

Pls. help me to find a way to display in the format of d:hh:mm
Thanks in advance
Moor
====================

Then I got the following code from some good people which is really
help me and it's working fine, no issue. which is below:

==============
Function ShowDuration (dtmStart as Date, dtmEnd as Date) as String
Dim lngDur As Long
lngDur = DateDiff("n", dtmStart, dtmEnd)
ShowDuration = Format(lngDur\1440,"00:") & _
Format((lngDur -(lngDur\1440)*1440)\60,"00:") & _
Format((lngDur -(lngDur\1440)*1440) Mod 60,"00")
End Function
================

Now I have to slim down that figure (Calculated to dd:hh:mm) to get the
Average of Telco outages. To do so, when I create a query and add the
query name "qryDuration" and add the fields such as "month", "Country",
"Telco" & "Duration (dd:hh:mm).

When i try to select the 'Avg' in the "Duration" field while other
fields are grouped by, I get the message 'Data Mismatch error'.

Someone pls. shed the light on how should I have to go about. below is
a real example:

Month Country Telco Duration(calculated field using
above showduration function)
01-Aug Austalia Optus 01:01:25
05-Aug Austalia Optus 00:02:00
09-Aug Australia C&W 01:00:10
11-Aug Australia C&W 00:00:30

What i try to do is I have multiple countries with multiple Telco. I
need to find outage duration of outages, which already done. next thing
is I need to find out by monthly and by country and by telco the counts
of each Telco and the average of every telco's outage duration.

Pls. help to solve this problem.

Thanks in advance
Moor

 

[Guest]

The result of ShowDuration is a string. And, because it contains non numeric
characters, VBA cannot coerce it into a number to do an average. If you want
to do the average, you will have to use the Duration as a number. If you
want to display it formatted, you will need to have two fields. One in the
original number format to do the math and one using the function to display
it as you are doing now.

 

[Moor]

Hi Klatuu,

But I am stuck here. To use number format is ok. In the previous
function it is the result we derive from getting the difference from
time and to show in a required format, which is
datediff("n",dtmStart,dtmEnd).

Can someone explain in what exact way that should I modify the function
so i will get the average of the 'Telco outages times' and to get the
same result as before using 'ShowDuration' function.

Thanks in advance.