Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #12001

Re: Elapsed Time Between Records

Show all headers | View raw

Am 24.08.2016 um 19:52 schrieb croy:
> On Wed, 24 Aug 2016 14:14:02 +0200, Ulrich Möller
> <knobbi38@arcor.de> wrote:
>
>
>> No, the days will not be rounded. I think you pick up the wrong record
>> in the subselect. Without consideration of the startpoint try this sql
>> to make it clearer how it works:
>>
>> SELECT t1.CountSurvId, t1.SurveyDate,
>>      (
>>        SELECT TOP 1 t2.SurveyDate
>>        FROM tblCountSurv as t2
>>        WHERE t2.SurveyDate < t1.SurveyDate
>>        ORDER BY t2.SurveyDate desc, t2.CountSurvId
>>      ) AS PrevSurveyDate,
>>      DateDiff("d",PrevSurveyDate,t1.SurveyDate) AS DiffDays
>>
> >FROM tblCountSurv AS t1
>> ORDER BY t1.SurveyDate;
>>
>> Ulrich
> Ah, that got it!  Thanks.
>
> Is there a way to put DiffDays into d:h:n format?
>
The easiest way is to define a vba function in a module:

Public Function ElapsedTime(EndTime As Variant, StartTime As Variant) As 
Variant
   Dim dteInterval As Date

   If IsDate(StartTime) And IsDate(EndTime) Then
     dteInterval = EndTime - StartTime
     ElapsedTime = Int(CSng(dteInterval)) & " days " _
                 & Format$(dteInterval, "hh") & " Hours " _
                 & Format(dteInterval, "nn") & " Minutes " _
                 & Format(dteInterval, "ss") & " Seconds"
   Else
     ElapsedTime = Null
   End If
End Function

and call it in the sql like this:

...DateDiff("d",PrevSurveyDate,t1.SurveyDate) AS DiffDays, 
ElapsedTime([SurveyDate],[PrevSurveyDate]) AS Elapsed

adapted from https://msdn.microsoft.com/en-us//library/office/ff197413.aspx

Ulrich

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-22 13:04 -0700
  Re: Elapsed Time Between Records Patrick Finucane <patrickfinucanetx@gmail.com> - 2016-08-22 13:49 -0700
  Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-23 12:57 +0200
    Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 09:59 -0700
      Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 14:14 +0200
        Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-24 10:52 -0700
          Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 23:38 +0200
  Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-23 05:36 -0700
    Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 09:43 -0700
      Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-23 10:51 -0700
        Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 12:21 -0700
          Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-24 02:14 -0700
        Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 11:25 +0200

csiph-web