Help Required to calculate Hours and Minutes in a Query

[aftab1965]

Dear Sir,
I want to calculate time ( Based on Start Time and End Time) in a query (Sample db is attached) I tried couple of methods to achieve the required result.
By setting up field to "Number" in Mehtod1 Table calculates as number but can't set as HH:NN. Method 2Table can't restrict input more than 60 Minutes.

Your Kind Advise/ Help are really appreciated

 

[plog]

I don't understand where to start, nor end. You've dumped 3 tables and 4 queries that presumably don't work on me. Which table is the starting data? I can't help you specificaly, so in general you use the DateDiff function:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

Since the smallest unit you want to use is minutes, you would get the difference in that unit, then do math to format the output like you want.

 

[theDBguy]

Try this one.

Date/Time: Calculate differences in time

 

[aftab1965]

I don't understand where to start, nor end. You've dumped 3 tables and 4 queries that presumably don't work on me. Which table is the starting data? I can't help you specificaly, so in general you use the DateDiff function:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

Since the smallest unit you want to use is minutes, you would get the difference in that unit, then do math to format the output like you want.

Thanks for replying. As I mentioned in my post that I have tried 2 methods. ( Table 1 and Table 2) but required help is as per " Required Table". Thanks if you can help me

 

[The_Doc_Man]

Let's start with the obvious. Time2 - Time1, where both variables are Date/Time formats, will give you the time difference in Access internal time units, which are days and fractions of a day. Typically when formatting the difference, you have one of two cases.

First case: The amount of time is less than 1 full day. In that case, the difference between two date fields can be input to a regular FORMAT function using "HH:NN" as the format string.

Second case: The amount of time is more than 1 full day OR can be negative. In that case, you have to do a formatting function because the standard Access routines do not allow hours in excess of 23.

Therefore, to help you, we first need to know if your times can exceed 1 day, either individually or in summation.

 

[Pat Hartman]

The datetime data type is a "point in time". It is NOT "elapsed time". Therefor, when you use datediff() to find the different between two dates, you can speify a unit of measure for the difference, seconds, minutes, hours, days, etc. But you don't get to specify a formatted result such as hh:mm.

I've attached a database with a number of date time functions that will help you to come up with a STRING that shows the result. Keep in mind that since the value is a string, it cannot be used to perform arithmetic.

 

[aftab1965]

Let's start with the obvious. Time2 - Time1, where both variables are Date/Time formats, will give you the time difference in Access internal time units, which are days and fractions of a day. Typically when formatting the difference, you have one of two cases.

First case: The amount of time is less than 1 full day. In that case, the difference between two date fields can be input to a regular FORMAT function using "HH:NN" as the format string.

Second case: The amount of time is more than 1 full day OR can be negative. In that case, you have to do a formatting function because the standard Access routines do not allow hours in excess of 23.

Therefore, to help you, we first need to know if your times can exceed 1 day, either individually or in summation.

Thanks Sir, my requirement is as per "Second Case". I'm calculating individual time which can't be a negative value and will not exceed 1 day(23:59) but in summation it will exceed 1 day. for easy understanding I attached db herewith

 

[theDBguy]

Thanks Sir, my requirement is as per "Second Case". I'm calculating individual time which can't be a negative value and will not exceed 1 day(23:59) but in summation it will exceed 1 day. for easy understanding I attached db herewith

Did you try my suggestion? Just curious...

 

[arnelgp]

query3 will be your final query.

 

[aftab1965]

query3 will be your final query.

Bundle Of Thanks Sir, It works for me. Again Thanks