By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Calculate difference in times in seconds

P: 28
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
Aug 17 '08 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 112
I would check out the DateDiff function built in to access for example to calculate the above would be:
Expand|Select|Wrap|Line Numbers
  1. DateDiff("s", #09:05:00#, #09:00:00#)
  2.  
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.
Aug 18 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 18 '08 #3

P: 28
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
Aug 18 '08 #4

NeoPa
Expert Mod 15k+
P: 31,271
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.
Aug 20 '08 #5

Expert 100+
P: 112
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.
Aug 20 '08 #6

P: 69
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.
Aug 21 '08 #7

100+
P: 167
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.
Aug 21 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.