Calculate difference in times in seconds

[orajat]

hi,

how do i calculate difference in time in seconds (00:09:05 - 00:09:00 = 300) where start time being a field and the difference in time is calculated in AHT field, ie last record minus previous record. this should be a continious excercise in ms access database table

[janders468]

I would check out the DateDiff function built in to access for example to calculate the above would be:

DateDiff("s", #09:05:00#, #09:00:00#)
 

This will return -300 (absolute value is 300 seconds), the 's' tells it to do the return result in seconds, there are other parameters you can give but the help files explain much better than I can so I'll leave that to you. Also, I changed the problem slightly because the value you gave appears to be a difference of 5 seconds, whereas it looked like you were trying to get 5 minutes (300 seconds). Hope that helps.

[missinglinq]

To calculate lapsed time you would use

DateDiff("s",StartTime, EndTime).

You don't do it backwards

DateDiff("s", #09:05:00#, #09:00:00#)

and then take the absolute value! You'd simply use

DateDiff("s", #:09:00:00#, #09:05:00#)

The OP has, indeed, posted times of 9 minutes and 9 minutes and 5 seconds after midnight, which from his posted expected results is incorrect. But this is only the beggining of the problems with this post.

how do i calculate difference in time in seconds... where start time being a field and the difference in time is calculated in AHT field, ie last record minus previous record. this should be a continious excercise in ms access database table

orajat, you're simply going to have to do a much better job of explaining what you're trying to do here, in order for us to give you any real help. This explanation simply makes no sense as written. You have to remember that you can see your database but we cannot. We're completely dependent on your explanation.

In particular you need to clarify "last record minus previous record. this should be a continious excercise in ms access database table"

You need to help us if you want us to help you!

Welcome to Bytes!

Linq ;0)>

[orajat]

this is what i'm trying to do...the start time will auto populate from system time everytime a new row is added. when that happens, the aht field would calculate the difference in time....aht = starttime(2nd record) - starttime(1st record), then 3rd record - 2nd record and so on an so forth.

StartTime Aht
3:28:52 AM 330
3:34:22 AM 108
3:36:10 AM 232
3:40:02 AM 0

[NeoPa]

If you are talking of records sequenced in order of the StartTime field (note there is no date element in the consideration - almost certain to throw this in a mess) then you will need to process through a sorted recordset using either DAO or ADODB (Basic DAO recordset loop using two recordsets may help with that).

Processing through the records in code is necessary as standard SQL routines have no concept of order within records.

[janders468]

Thanks Linq,

I was posting in the event that you were interested in finding the absolute value for the duration of tasks you complete before you begin. Thanks for the correction, foolish mistake on my part.

[youmike]

Don't forget that in Date/Time fields Access handles date as the integer portion of the field and time of day as the decimal fraction. Therefore, if you take the difference beteewn two date/time fields and multiply this by 86400 - the number of seconds in a day - you will get the number of seconds in the date/time difference.

[hjozinovic]

In order to 'join' the previous record time with current record time try looking here .
We discussed similar thread and I believe you could find it helpfull.