Average Date/Time Field

[supportt]

I am trying to run a query against my database that will average the date/time fields. I have two fields, one is an open date/time and the other is a closed date/time. I would like to be able to select a date range and average the open closed time. In other words, I want to see all tickets for the month of 02/2002 and average the amount of time they were opened. Any HELP! would be very much appreciated.

Thanks
David

 

[KKilfoil]

Create a select query (lets call it qryGetTimeOpened). Add the quert/table where your two date fields are. add a calculated field as follows:

TimeOpened: [DateClosed] - [DateOpened] {or whatever your fieldnames are}

Under the appropriate date field (you didn't say whether the month of 02/2002 was for dateopened or dateclosed), add a criterion like 'Between [Enter start date] and [Enter finish date]' (without the quotes!)

This will select your date range, and calculate the openedtime per record.


Now, create a summary query using qryGetTimeOpened to get your average, count, etc.

 

[Pat Hartman]

Simple subtraction won't cut it in this case since the default interval is days. You need to use the DateDiff() function with an interval of minutes or seconds depending on how detailed you need the answer to be.