Calculating elapsed time over a 24 hour period

[kcmiuser]

I am creating a little app to run a report to show elapsed time on jobs for certain employees over date ranges. I have the pass thru query functioning and bringing in all the data I need to create the reports, my problem is gettign the elapsed time value correct. I am pretty good in excel, but apparantly not so much in access.
The data comes into the wquery in the following manner
fsdatetime - 4/7/2014 7:30:00 AM (general date format)
fedatetime - 4/7/2014 1:30:00 PM
Which is 6 hours of elaposed time
So, I thought you would just subtract the 2 fields 9as you can in Excel) using an expression. Expr1 = [fsdatetime]-[fedatetime] - WRONG, I get all sorts of negative numbers that make no sense, which tells me I must need to convert the incomgin data to another format or something else.
I just do not know what the something else is???? Very confused!!!


Finally, there may be a situation where this rolls over at midnight.
Thansk in advance

[amrut]

You have to use Datediff function with "h" ( for hours) or "n" ( for minutes) See here for more help http://allenbrowne.com/casu-13.html

[kcmiuser]

I am creating a little app to run a report to show elapsed time on jobs for certain employees over date ranges. I have the pass thru query functioning and bringing in all the data I need to create the reports, my problem is gettign the elapsed time value correct. I am pretty good in excel, but apparantly not so much in access.
The data comes into the wquery in the following manner
fsdatetime - 4/7/2014 7:30:00 AM (general date format)
fedatetime - 4/7/2014 1:30:00 PM
Which is 6 hours of elaposed time
So, I thought you would just subtract the 2 fields 9as you can in Excel) using an expression. Expr1 = [fsdatetime]-[fedatetime] - WRONG, I get all sorts of negative numbers that make no sense, which tells me I must need to convert the incomgin data to another format or something else.
I just do not know what the something else is???? Very confused!!!
Finally, there may be a situation where this rolls over at midnight.
Thansk in advance

That works perfectly, however, one last item. In the report, I used the formatting to show as hours and minutes and it worked great, however, I would like show the subtotal in the report as minutes and hours as well, all I have are minutes and my attempts at converting aren't going so well, I thought I could just nest the expression =[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00") somehow in the total, but not working as planned.

[amrut]

I do not know if there is a function to total hour/minutes. A workaround I can suggest is add one more field to your query to find the difference in minutes only. Add that field to the report, set its visibility to hidden, sum it and the use some simple maths to display data in desired format. Experts may provide you an efficient alternative method.