How To Calculate The Minutes Between Two Different Times In A Query

[Nicholas Davenport]

Hi, I hope someone can help me

I have a Callback database that logs the time that a customer has called our department and have been unable to contact who they where after. When this happens a Callback would have been logged onto the database system. The information is logged and stored in my main table. I then have another table that records all the attempts that have been made by an agent to contact the customer back. These two tables are linked together using a MessageID auto number. This works fine, my issue is that i would like to produce a query that can calculate the turn around time from the customers first call to the time an agent has tried to contact the customer back.

For example:
MessageID SaveDate SaveTime Agent
173570 14/09/2010 14.13 Joe

MessageID AttemptID Attempt AttemptDate Attempttime Agent
173570 80681 1 14/09/2010 15.18 Sam

For some reason when i post my example it removes the spaces between the fields. In this example the turn around time would be 65 minutes. How can I calculate the time difference in minutes from 14.13 to 15.18 into a column at the end of my query.

Any help would be greatly appreciated

[liimra]

Solution/

Just use

DateDiff("n", [SaveTime], [Attempttime])

Since you have the two tables linked through MessageID, you don't have to do anything else. Create the Query with "SaveTime" & "Attempttim e" and any additional fields and add one column to your query in design view and insert this in the "Field"

Duration: DateDiff("n", [SaveTime], [Attempttime])

Regards,
Ali

[TheSmileyCoder]

For some reason when i post my example it removes the spaces between the fields. In this example the turn around time would be 65 minutes. How can I calculate the time difference in minutes from 14.13 to 15.18 into a column at the end of my query.

If you enclose the text you want to line up in code tags, the whitespace will be kept:
[code]my tables go in here[/code]
For example:

MessageID AttemptID Attempt AttemptDate Attempttime Agent 
173570    80681     1       14/09/2010  15.18       Sam

[Nicholas Davenport]

I managed to figure the code out before i got to see your reply, but thanks for your response, its greatly appreciated