Calculate average of times

[Alejandro]

Hello,

I have a query that extracts the top 5 processing times of a process. It is
a basic query that substracts the finish time to the starting time:

ProcessingTime: finish_time - start_time

and I constraint the Top Values function to 5. Nothing wrong until that point.

But in a second query, where I take that first query and simply do Show:
Avg, I get an error message when I try to run it. I think it may be that the
format for the results of the first query is not the appropriate. I already
tried changing the formula for the processing time to:

ProcessingTime: Format(finish_time - start_time, "Short Time")

But it didn't work either. Any ideas on what's wrong?

Thanks!

AP.

 

[Dale Fye]

Alejandro:

What is the datatype of these fields? If they are date/time fields, then
Access is probably retaining the Date/Time field type in the calculated
field, in which case I think you need to force your ProcessingTime to be a
datatype (double).

ProcessingTime = cdbl(finish_time - start_time)

What is the magnitude of the calculated ProcessingTime (minutes, hours,
seconds)? If these fields are date/time, then the resulting subtraction
might not mean much to you (.5 = 12 hours), so you might want to multiply by
24 to get the number of hours, or by 1440 to get the value in minutes, then
when you average them the top 5, you will get a meaningful number.

HTH
Dale

 

[Klatuu]

ProcessingTime: finish_time - start_time
will not give the results you expect.
Depending on what level you want to caclulate, you can choose hours,
minutes, or seconds using the DateDiff function. I suggest seconds, then you
can calculate the hours, minutes, and seconds if you need to.

ProcessingTime: DateDiff("s",start_time, finish_time)

 

[Alejandro]

Thanks!

Alejandro:

What is the datatype of these fields? If they are date/time fields, then
Access is probably retaining the Date/Time field type in the calculated
field, in which case I think you need to force your ProcessingTime to be a
datatype (double).

ProcessingTime = cdbl(finish_time - start_time)

What is the magnitude of the calculated ProcessingTime (minutes, hours,
seconds)? If these fields are date/time, then the resulting subtraction
might not mean much to you (.5 = 12 hours), so you might want to multiply by
24 to get the number of hours, or by 1440 to get the value in minutes, then
when you average them the top 5, you will get a meaningful number.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.