 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / December 2005Calculating time difference problem
Hi, I have a table, with times stored for comptetors in races, With the kind help of Allen Brown, I am now successfully storing in a query the time for the competitor, and the previous competitors time, with the aim of showing a time difference Time are stored thus 05:32:50 (5 hours, 32 minutes and 50 seconds) I'd like to return in a separate column, the time current competitor was behind the previous So, I used Diff: DateDiff("n",[Priortime],[time])/60 However with values for Priortime and time as 05:42:04 and 05:42:30 I get the result 0 (should be 26 seconds!) and for 05:42:04 and 05:43:39 gives the answer 0166666666666667 (should be 1 minute 35 seconds) If I reverse the two fields in the equation I just get the same as above with '-' (negative) values What am I doing wrong? I'd really like to get an answer formatted 00:00:26 Thanks for any help Neil >Hi, > [quoted text clipped - 19 lines] > >What am I doing wrong? DateDiff returns a value in integer seconds, minutes, years or whatever argument you pass. If you use the "n" argument, you'll get integer minutes: 0:0:00 to 0:0:29 will give 0, to 0:0:30 will give 1. To get seconds, and display them as hours:minutes:seconds, use DateDiff("s", [PriorTime], [Time]) \ 3600 & ":" & Format(DateDiff("s", [PriorTime], [Time]) \ 60 MOD 60, "00") & ":" & Format(DateDiff("s", [PriorTime], [Time]) MOD 60, "00") Thus if Charmus is behind by 3664 seconds, you'll see 1:01:04. John W. Vinson[MVP] Quibble, DateDiff has always just given me boundaries crossed. So minutes between 00:00:00 to 00:00:59 returns 0 not 1. > >Hi, > > [quoted text clipped - 33 lines] > > John W. Vinson[MVP] >Quibble, > >DateDiff has always just given me boundaries crossed. So minutes between >00:00:00 to 00:00:59 returns 0 not 1. Thanks John - right you are of course! John W. Vinson[MVP] Thanks John, just what I needed. Of course the first will be need to be blank, and at the moment just returns :: so can I build in something to make it 00:00:00? Thanks Neil > >Hi, > > [quoted text clipped - 33 lines] > > John W. Vinson[MVP] >Thanks John, just what I needed. >Of course the first will be need to be blank, and at the moment just >returns :: so can I build in something to make it 00:00:00? Use NZ(DateDiff(...)) in place of just plain DateDiff. John W. Vinson[MVP] Thanks once again John, This forum has proved most helpful. For the record, I modified the statement slightly to this: Diff: Format(nz(DateDiff("s",[PriorTime],[Time])\3600,0) Mod 60,"+00") & ":" & Format(nz(DateDiff("s",[PriorTime],[Time])\60,0) Mod 60,"00") & ":" & Format(nz(DateDiff("s",[PriorTime],[Time]),0) Mod 60,"00") as I wanted 2 leading zero's and a + sign Thanks again Neil > >Thanks John, just what I needed. > >Of course the first will be need to be blank, and at the moment just [quoted text clipped - 5 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.