Average Time Calculation using Military Time.

[Mattine]

I put this code into a module in Access. This calculates a time variance. i.e.The difference between a start time and an end time. How could I create a new module either using this code and altering it to show an average amount of time?
Option Explicit
Function GetElapsedTime(interval)

Dim totalhours As Long, totalminutes As Long, totalseconds As _
Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As Long

days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60

GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & _
" Minutes " & Seconds & " Seconds "

End Function

 

[JerryDennison]

You'll need to get the average first then pass this to your function. Create a totals query that can calculate the average elapsed interval then pass this value to your function.

GetElapsedTime(AverageInterval)

 

[Mattine]

Dear Jerry,
How do I create a totals query to calculate the average elapsed interval and then pass the value to my function?

Thank-you!

Sincerely,

~Mattine

 

[JerryDennison]

I guess it all depends on how you want your average grouped. If all you want is the average of all intervals in the entire table then you need only have one field in the queries QBE grid. Create a query based on the table that has the interval value. Place this field on the QBE grid(if the interval is a calculated value then put the calculation on the grid). Press the Sum button on the tool bar. In the total property change it from the default of Group By to Avg. Test the query by running it. If it gives you the result expected you can then do one of two things. Save this query and use it as the basis for your formatting conversion or you could simply use your function here.

 

[Mattine]

Dear Jerry,

Actually, I have two fields: StartTime1 and EndTime1. I get the time elapsed between the two fields, using the code in the module above. I was wondering if there would be any way to show the average time it takes between the Start time and end time which is formatted in General Time, military time to show in a report. If it could just sum all of the time that shows in the report and then divide by the number of records...Can you help? Would a QBE be able to do that?

Thank-you for the help!

~Mattine

 

[JerryDennison]

What format is StartTime1 and EndTime1 in? Your function only accepts a single parameter so how are you arriving at this value and passing it to the function? At any rate, if the two values you're using and whatever calculation between them works you have everything you need. Simply find the Average interval before passing it to the function. This can be done using a totals query.

 

[Mattine]

How would I find the average interval before passing it through to the function using a totals query?

 

[JerryDennison]

E-mail me your db. I'll add a query to illustrate.

jerrydennison@yahoo.com