It is not any problem to calculate averages on dates, as dates are stored as
datevalues. But the result will be displayed as date value - not in date
format.
But I guess that your field "Time Taken" is a field with numeric value.
Anyway this formula in your query for the report should work:
Avg(TimeTaken),
And yes put it in your query. Reports are good for layouts, and queries are
good for calculations.
To calculate different between to dates or times, use the DateDiff function.
It returns a numeric long integer whatever you asks it to do. Here the
difference is in days, but you can calculate in everything from years to
seconds.
DateDiff("d",[DeliveriLine]![PlGI date],[DeliveriLine]![AcGI date])
You might run into another problem: You wan't to calculate the difference in
workingdays - leaving out saturdays and sundays. In Microsoft Excel you
will find in an add on library the function NetWorkDays. To add to your
frustration: A similiar function is not available in Access and has to be
created as a user defined function.
Brgds
Rolfern
"Simon Bailey" <go*********@hotmail.com> wrote in message
news:1c*************************@posting.google.co m...
I currently have a query calculating the gap in days between two
dates. The fields being "DateLookedAt" and "DateResolved" plus the
calculated field "TimeTaken".
I am looking to add another field to my report (or does this have to
be created in the query?) that calculates the average of all the data
in the "Time Taken" field.
SQL code or some tips would be much appreciated.
Many Thanks
