Working Out Total Time Spent (In A Query)

[CharlesWhiteman]

In My Db I have TblSupportTickets and a Table which records support ticket activity. When adding a new activity detail to the Db it automatically records when the FrmActivityDetail opens and on closing, it records the time the Form was closed. So in TblSupportTickets which relates by ID to TblSupportTicketActivity on a one-to-many relationship.

I then created a query with SupportTicketID, TimeOpen, Time Closed. SupportTicketID criteria is Forms.FrmSupportTicket.txtSupportTicketID

This lists all the time open's and timeclosed's for that support ticket.

how can I work out the total time spent on that support ticket?

 

[namliam]

By doing something like TimeClosed - TimeOpen ???

 

[CharlesWhiteman]

I suggest you try that! As you may imagine i have already.

 

[Rabbie]

I suggest you try that! As you may imagine i have already.

Remember how Access stores date/Time fields. The time part is store as the decimal fraction part and the date as the number of days since 1/1/1900.

So if you are getting a very small number you will need to use Format to put it into HH:MM:SS

Hope this helps

 

[namliam]

I suggest you try that! As you may imagine i have already.

I dont have to try it, because I know it works...

What is going wrong in your case?
Are both fields date/time fields
What Rabbie said, is there a small difference (secs/mins?)?

Just because you cannot make my suggestion work doesnt mean it is "worthless"

Making use of the TimeClose - TimeOpen and doing a group by/sum in your query you should be able to get the result you desire.

 

[Rabbie]

Remember that even 8 hours will only display as .333 unless you reformat it to hours/Minutes.