Calculating date difference between different records

[billcam]

How do I calculate the difference between the same date field from record to record. I have an employee job classification table and want to know how long they were in each position using the current record date as the start date and the next record date as the end date.

 

[LonnieJohnson]

Hey billcam,

I found your request interesting. I gave it a stab.

I created a table with a field called xdate and a field called duration. I put random dates in the field xdate and nothing in duration.

I created a module that iterates thru the records. During it's iteration it
1. sorts the table by the date field
2. checks to see if it is on the first record
3. if so, then it sets the value of two variables (FromDate and ToDate) to the same date. This will allow a date difference of 0 for the first record.
3. for all subsequent records it sets FromDate = ToDate then
it sets ToDate to the date in the record. Now the FromDate is equal to the date in the prior record and the ToDate is equal to the date in the current record.
4. Using the DateDiff funtion between FromDate and ToDate we get the value to udpate to the Duration field. Now we now how much time elapsed between each record.

Here is the code. If you have access97, I can send you a sample dB.

Public Sub HOWLONG()

Dim intCnt As Integer
Dim db As Database
Dim rs, rsSort As Recordset
Dim FromDate, ToDate As Date

Set db = CurrentDb
Set rsSort = db.OpenRecordset("dates", dbOpenDynaset)

rsSort.Sort = "xdate"
Set rs = rsSort.OpenRecordset()

intCnt = 1

Do Until rs.EOF
With rs
.Edit

If intCnt = 1 Then
FromDate = rs!xdate
ToDate = rs!xdate
Else
FromDate = ToDate
ToDate = rs!xdate
End If

rs!duration = DateDiff("d", FromDate, ToDate)

.Update

End With

rs.MoveNext

intCnt = intCnt + 1
Loop

End Sub
ljprodev@yahoo.com
ProDev, MS Access Applications

http://www.galaxymall.com/software/PRODEV

 

[MichaelRed]

this topic was covered recently in these (Ms. Access forums) See thread701-101152 for one approach. You can (should?) also be able to find this using the search with "previous records" or "self join".

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over