Averaging date/time fields with a query

[DennisJones]

I have a table in which each record includes a field showing the time that an event started and then another field which stores "how long did it take", these two fields are both defined as a short Time. I then have a query that runs through the "how long did it take" fields to produce an average time for the whole sequence of events. Two problems:

1. The average comes out as a decimal like 39553.367942 instead of several hours and minutes.

2. When I try to access this value using DLookup on a form I get "#error"

Any suggestions?

thanks

 

[ajetrumpet]

Dennis,

If you use functions like AVG() or DAVG() over a range of numbers, you will get that. To get a difference in time, use the DATEDIFF() function. Check out the syntax in the Access help menu. "h" is for hours, which is what you will probably want. If you want to mix hours with minutes, you might need more help. Post back if you need help with it...

 

[Brianwarnock]

39553.367942 is 15/04/2008 08:49:50 UK format.

Does that give you a clue as to what you are doing wrong?

Brian