Date and Time Calculations

[roseannej78]

I have a table in Access that has the following fields: ID, DateIn, TimeIn, DateOut, TimeOut. I want to create a query that has these fields and TotalTime Column. In the TotalTime, I need a time difference calculation based on dates that shows the total hours and minutes for each ID. The format should be in hh:mm. The following table is from an Excel. I know how to do the calculation in Excel, but not in Access.
EX.
ID DateIn TimeIn DateOut TimeOut TotalTime
13012342 9/3/2009 10:17:00 AM 9/3/2009 10:17:00 AM 0:00
13012343 9/4/2009 9:56:00 AM 9/5/2009 9:59:00 AM 24:03
13012344 9/3/2009 8:32:00 AM 9/3/2009 8:39:00 AM 0:07
13012345 9/3/2009 10:58:00 AM 9/14/2009 1:47:00 PM 266:49
You notice the TotalTime Column exceeds 24 hours, which is what I want.
Thanks in advance for your help.

 

[boblarson]

It is a little extra work because the date and time are in different fields. But you would create a few extra fields in your query by adding the two together or you could do it in one long output.

If creating separate fields (in the query):

DateTimeIn:[DateIn] + [TimeIn]

DateTimeOut:[DateOut] + [TimeOut]

TotalElapsedTime: DateDiff("n", [DateTimeIn], [DateTimeOut])\60 & ":" & DateDiff("n", [DateTimeIn], [DateTimeOut]) Mod 60

Or all in one shot:


TotalElapsedTime: DateDiff("n", [DateIn] + [TimeIn], [DateOut] + [TimeOut])\60 & ":" & DateDiff("n", [DateIn]+[TimeIn], [DateOut] + [TimeOut]) Mod 60

 

[asad.saeed]

kutte k bache teri maa ki choot

 

[roseannej78]

Thank you, boblarson! It worked great!

 

[roseannej78]

It is a little extra work because the date and time are in different fields. But you would create a few extra fields in your query by adding the two together or you could do it in one long output.

If creating separate fields (in the query):

DateTimeIn:[DateIn] + [TimeIn]

DateTimeOut:[DateOut] + [TimeOut]

TotalElapsedTime: DateDiff("n", [DateTimeIn], [DateTimeOut])\60 & ":" & DateDiff("n", [DateTimeIn], [DateTimeOut]) Mod 60

Or all in one shot:


TotalElapsedTime: DateDiff("n", [DateIn] + [TimeIn], [DateOut] + [TimeOut])\60 & ":" & DateDiff("n", [DateIn]+[TimeIn], [DateOut] + [TimeOut]) Mod 60

I am creating a report now based on the "TotalElapsedTime". But I can't seem to give a sum for each ID group as well as giving the grand total at the end of the report. Thank you.