Summing a list of time values

[bgcogen]

Hi.

I really need to add a list of time values in a query but I continually get all kinds of different error messages. What am I doing wrong??

I am able to subtract two values from each record in the query [Opening and closing time], I use the expression Closed_time: Format([Closing time]-[Opening time],"Short Time") and everything is grand. But then I need to sum all these values but I keep on getting errors like SQL and "you tried to execute a function which doesn't have the closed_time aggregate function.

Do you have any ideas????

Thanks a million.
Declan

 

[clive2002]

have you tried saving the query at the point when everthing is grand and using another query to sum the time values, or just summing the opening and closing time and then using your formula when already summed

 

[Pat Hartman]

You can't format the elapsed time prior to summing it. Use the DateDiff() function to get the difference in seconds or minutes depending on your requirements. Sum this amount and then format the result.

Sum(DateDiff("n",[Closing time],[Opening time]))

This will give you the total minutes. I don't think that you'll have any success using the Format() function to format this into ShortTime format since you are likely to exceed 24 hours. I think that you'll have to do it the old fashioned way using division. There are several articles on this subject available from the Microsoft knowledge base.