Need help creating a query...

[105ben]

Hello,

I have a table that stores two dates inside it, I want to find the difference between those dates for each record, then find the average of all the differences...

The user enters the date range (for the first date field of the two in the table) using text boxes on a form.

I've attached what I have done so far... which doesnt work

Thanks in advance!

 

[Brianwarnock]

"which doesn't work". Is not very informative so for the moment I will restrict my reply to saying that the Group By under time occurred should be Where

Brian

 

[105ben]

Sorry, it just doesn't return any data... (there is data in the table within the range so it should)

Good spot, changed the where but still no data..

 

[105ben]

It's now returning data.

Is there anyway to bring it back in a different format? currently its just in days. I'd like days and hours if possible? The date stored is in DD/MM/YYYY HH:MM

 

[Pat Hartman]

If you want to find the average, you will need to set the difference to return hours. Only the final result should be converted to days and hours.

I posted a sample database that has functions to do what you need.

http://www.access-programmers.co.uk/forums/showthread.php?t=238821