Calculate time difference

[desireemm1]

Hello I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010. Can anyone help please

SELECT [Message Tracking_tbl].[Date & Time Left], [Message Tracking_tbl].[Date Returned], [Message Tracking_tbl].ID
FROM [Message Tracking_tbl];

[June7]

Use DateDiff() function.

Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

[desireemm1]

Thank you June7 I appreciate it, the database isn't mine its someone elses

[desireemm1]

thank you so much for your reply, the only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over. Can anyone please help
thank you

[June7]

If the fields are date/time type and have a full date/time value, the DateDiff function should work.

Not understanding the data structure. Provide example. Can build a table in the post or attach file. Click Go Advanced below the Quick Reply.

[desireemm1]

its all based off one table, I didn'[t create the database the name of the table is [Message Tracking].[Date & Time Left]
and [Message Tracking].[Date Returned]


[img]

[desireemm1]

SELECT [Message Tracking_tbl].ID1, [Message Tracking_tbl].ID, [Message Tracking_tbl].[User Retrieving], [Message Tracking_tbl].[Date Retrieved], [Message Tracking_tbl].[Date & Time Left], [Message Tracking_tbl].Caller, [Message Tracking_tbl].[Customer Type], [Message Tracking_tbl].Requester, [Message Tracking_tbl].[Patient Name], [Message Tracking_tbl].[Request ID], [Message Tracking_tbl].[Date of Birth], [Message Tracking_tbl].[Contact Information], [Message Tracking_tbl].Notes, [Message Tracking_tbl].Priority, [Message Tracking_tbl].[User Returning], [Message Tracking_tbl].[Date Returned]
FROM [Message Tracking_tbl];

[June7]

I was looking for sample data. If you tried to attach an image it didn't work. Not seeing any attempt to use DateDiff in that query.

DateDiff("h", [Date & Time Left], [Date Returned])

[desireemm1]

Date & Time Left	Date Returned	Expr1
6:27 AM	10/23/2014	-6
7:28 AM	10/23/2014	-7
7:22 AM	10/23/2014	-7
10:42 AM	10/23/2014	14
9:35 AM	10/22/2014	-9
9:20 AM	10/22/2014	-9
9:02 AM	10/22/2014	-9
7:35 AM	10/22/2014	-7
7:08 AM	10/22/2014	-7
6:50 AM	10/22/2014	-6
6:50 AM	10/22/2014	-6
6:35 AM	10/22/2014	-6

[orange]

Please post the query sql that gave you this output.

[desireemm1]

Sorry about that should have done that earlier


SELECT [Message Tracking_tbl].[Date & Time Left], [Message Tracking_tbl].[Date Returned], DateDiff("h",[Date & Time Left],[Date Returned]) AS Expr1
FROM [Message Tracking_tbl]
WHERE ((([Message Tracking_tbl].[Date Returned]) Is Not Null));

[June7]

Where is the date part of [Date & Time Left] and where is time part of [Date Returned]? Are Leave and Return in the same day? Should we assume the Return time is 5 pm?

[desireemm1]

that's my problem the field Data Type was in text and not Date/Time that's my problem. I cant tell the time of when a user returned a call and thats what I have to find out

[June7]

Can't 'find out' the time call was returned - not from the data as shown - it must be data input to the table.

Didn't answer my other questions.

[desireemm1]

Can't 'find out' the time call was returned - not from the data as shown - it must be data input to the table.

Didn't answer my other questions.

Here is the data from both fields the data type in [Date & Time Left] is Date/Time and the Data type in Date Returned is Text

Date & Time Left	Date Returned
10/23/2014 6:27:00 AM	10/23/2014
10/23/2014 7:28:00 AM	10/23/2014
10/23/2014 7:22:00 AM	10/23/2014
10/22/2014 10:42:00 AM	10/23/2014
10/22/2014 9:35:00 AM	10/22/2014
10/22/2014 9:20:00 AM	10/22/2014
10/22/2014 9:02:00 AM	10/22/2014
10/22/2014 7:35:00 AM	10/22/2014
10/22/2014 7:08:00 AM	10/22/2014
10/22/2014 6:50:00 AM	10/22/2014
10/22/2014 6:50:00 AM	10/22/2014
10/22/2014 6:35:00 AM	10/22/2014
10/22/2014 6:34:00 AM	10/22/2014
10/21/2014 4:37:00 PM	10/22/2014