difference of date/time field

[khawarameer]

Dear All,

I have a table of 20000 records. Records have a datetime field. I want to select all records where gap between one record and subsequent record is more than one hour [condition to be applied on datetime field].

can any one please help.

regards

KAM

 

[Micron]

so you can check each record, though not necessarily "in order" -

Maybe I was wrong to assume the records had to be in chronological order, which is why my mindset was around ensuring an order first. That being said, your solution could be applied to a query (as a table) if that was a concern. I had to do something like this once where the in-out date/time fields related to each individual had to be compared to ensure there was a minimum gap between the out and the next in, plus ensure the total for the day didn't exceed 16 hrs for any individual, plus look for double payment - and the in-outs for an individual could overlap other individuals. I tend to gravitate to what I've done before, I guess. Here's my take on the vb approach, untested, unfinished, mostly due to not having the required information. I had in mind to output the result to a table instead of a virtual recordset in order to avoid having to use ADODB. It might be interesting to see which is faster, though I expect the sql version would be.

Private Sub TimeSpan()
Dim rsSource as DAO.Recordset, rsTarget As DAO.Recordset
Dim dteStart as Date, dteEnd as Date
On Error GoTo errHandler

Set rsTarget = CurrentDb.OpenRecordset("NameOfTable", dbOpenDynaSet)
Set rsSource = CurrentDb.OpenRecordset("NameOfSortingQuery")
rsSource.MoveFirst
Do While Not rsSource.EOF
  dteStart = rsSource.Fields("StartDate")
  rsSource.MoveNext
  dteEnd = rsSource.Fields("EndDate")
  If DateDiff("h",dteEnd, dteStart)>1 Then
    rsSource.MovePrevious
    rsTarget.AddNew
    rsTarget!RecID = rsSource.Fields("RecID")
    'add additional fields as required
    rsTarget.Update
    rsSource.MoveNext
    rsTarget.AddNew
    rsTarget!RecID = rsSource.Fields ("RecID")
    rsTarget.Update
  End If
Loop

exitHere:
rsTarget.Close 
rsSource.Close
Set rsTarget = Nothing
Set rsSource = Nothing
Exit Sub

errHandler:
msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub