calculate time greater than 24 hours using a query 1

[ksmith108]

I need to calculate time greater than 24 hours for some jobs. The table data is in date/time format but I can not get access to calculate job run time over that 24 hour hump.

The table data looks like this

Event Start time End time
job 1 7:24:09 AM 3:49:48 PM

I then run a query that gives me
Event Start time End time Run Time
job 1 7:24:09 AM 3:49:48 PM 08:25 hh:mm
Note the 8 hours 25 min run time

But in actual fact it should be

Event Start time End time Run Time
job 1 7:24:09 AM 3:49:48 PM 32:25 hh:mm
Note the 32 hours 25 min run time.

Here is my SQL
SELECT [Activity Log].Event, [Activity Log].[Start time],
[Activity Log].[End time], Format([start time]-1-[end time],"Short Time&quot
AS [Run Time]
FROM [Activity Log]
WHERE ((([Activity Log].Event)=" job 1&quot);

What am I missing, I have tried other answers to simular posts, but
have not had any luck.
Thanks
Kirk

 

[PaulBricker]

I don't see any DateField to help Access know that you've crossed the 24 hour mark. Am I missing something or do you not have a DateField.

Paul

 

[MinusM]

([start time]-1-[end time]
Is this the right logic you use for calculating runtime??

Instead of depending on Access for formatting do it youself
You can use datefields just like number fields and Find difference.
Try this SQL
SELECT [Activity Log].Event, [Activity Log].[Start time],
[Activity Log].[End time], Minute([end time]-[start time]) & ":" & Second(([end time]-[start time]) AS [Run Time]
FROM [Activity Log]
WHERE ((([Activity Log].Event)=" job 1&quot);


PS : Make sure your database is storing both date and time
I haven't checked the concatenation. Use Str(Minute(....)) if this generates any error

Best of luck

 

[ksmith108]

Thanks MinusM
That sql worked perfectly.
Your right, perhaps ([start time]-1-[end time] is/was not the right way to calculate time, but, hey, it worked for anything that ran under 24 hours. It was only jobs that ran over that 24 hour time where I ran in to issues.
Thanks again
Kirk