Calculate date gap from previous record to next 1

[MADDPM]

Can anyone suggest a bit of code to calculate the months between an end date in one record/row and the start date in the next record/row of a record set. The following is a synopsis of the problem area in my code:

Set rsA = CurrentDb.OpenRecordset("SELECT * FROM yada, yada, yada ....)

Dim intCalcGap1 As Integer
Dim intCalcGap2 As Integer
Dim intCalcGap As Integer

intCalcGap1 = rsA!End_Date
rsA.MoveNext
intCalcGap2 = rsA!Start_Date
rsA.MovePrevious
intCalcGap = DateDiff ("m", intCalcGap1, intCalcGap2)

If CalcGap > 1 then
rsAMoveNext
End If

I have no idea how to do this - any advice will be greatly appreciated.

Thanks a bunch-
colleen

 

[IanAble]

Hi Colleen,

This is how you calculate the running difference between records:

Public Sub TestDateDiff()
Dim dtEnd As Date
Dim dblDateDiff As Double

With CurrentDb.OpenRecordset("SELECT * FROM yada, yada, yada ....")
Do Until .EOF
If dtEnd > 0 Then
dblDateDiff = dtEnd - ![Start_Date]
dtEnd = ![End_Date]
End If
.MoveNext
Loop
End With
End Sub

I've left the date difference in fractions of days because you're going to have problems with fractions of days, or days less than 1 month returning 0, but more importantly, where the end date of the previous record is in one year and then the start date of the next record is in the next year...

It is possible to work this out but unless yo've got a very good reason for doing it it's a PITA!

 

[MADDPM]

Hi Ian,

I'm not having much success with your code - but it's late in the day and I'm a novice at this.

The code you provided would need to be the third decision-making step in a series of steps to determine if a record qualifies for inclusion in a running sum.

I tried putting the code in it's own sub as you have above and calling it at the third step. Then I tried putting the code directly at the point of the third step.

I guess in theory I understand what the code is doing - but in practice I'm not sure.

In my case my function finds an employee and finds their latest contract and the status (part-time or full-time) - then it moves to the next record in the recordset and decides whether or not the employee 1) had an additional contract, 2) if the contract is the same status and 3) IF there has been no gap in employment, then a running sum is kept.

If employee number changes, status changes, or if there is a gap in employment - the function needs to move on and reset the sum and string variables.

So what I essentially need is a "gap check" - calculated on the end-date of the previous contract compared to the start date of the next contract - making sure it isn't more than 3 months or 90 days thereabouts. If no gap - continue with process; if there is a gap - then start afresh.

Any suggestions? By the way - thank you for your time!
-colleen

 

[MajP]

Why use code, when you can do this simply in Sql?

dtmStartDate	dtmEndDate	id
1/1/2010        2/5/2010        1
4/10/2010       6/10/2010       2
9/11/2010       11/5/2010       3
1/1/2011        8/5/2011        4
6/1/2012        7/2/2012        5

SELECT 
  CurrentDate.dtmStartDate AS CurrentStartDate, 
  (select top 1 dtmEndDate from tblDates where ID < CurrentDate.ID Order by dtmEndDate Desc) AS PreviousEndDate,
   DateDiff("M",[PreviousEndDate],[CurrentStartDate]) AS Gap
FROM 
  tblDates AS CurrentDate;

CurrentStartDate  PreviousEndDate Gap
1/1/2010		
4/10/2010       2/5/2010            2
9/11/2010       6/10/2010           3
1/1/2011        11/5/2010           2	
6/1/2012        8/5/2011            10

 

[MajP]

I missed all of this

In my case my function finds an employee and finds their latest contract and the status (part-time or full-time) - then it moves to the next record in the recordset and decides whether or not the employee 1) had an additional contract, 2) if the contract is the same status and 3) IF there has been no gap in employment, then a running sum is kept.

If employee number changes, status changes, or if there is a gap in employment - the function needs to move on and reset the sum and string variables.

So what I essentially need is a "gap check" - calculated on the end-date of the previous contract compared to the start date of the next contract - making sure it isn't more than 3 months or 90 days thereabouts. If no gap - continue with process; if there is a gap - then start afresh.

Could you post some dummy data and what the expected answer would be? Difficulte to understand what you are doing.

 

[MADDPM]

Hi MajP,

I have provided sample data for one employee and the different situations an employee may encounter.

BeginDate ExpireDate Status
01-SEP-2001 31-MAY-2002 Full Time = 1 AY (academic year)
01-SEP-2002 31-MAY-2003 Full Time = 1 AY
At this point, this employee has worked 2 consecutive academic years in full-time status
Sum needs to reset because the next records indicate a change in status.
01-SEP-2003 31-MAY-2004 Part Time = 1AY
01-SEP-2004 31-DEC-2004 Part Time = .5 AY (fall semester)
01-JAN-2005 31-MAY-2005 Part Time = .5 AY (spring semester)
At this point, this employee has worked 2 consecutive academic years due to change to part-time status
Again, sum needs to reset because of a change in status.
01-SEP-2005 31-MAY-2007 Full Time = 2 AY
01-SEP-2007 31-MAY-2008 Full Time = 1 AY
At this point, this employee has worked 3 consecutive academicyears due to change to full-time status
And again, sum will need to reset because the next record will encounter a gap in employment.
01-SEP-2009 31-MAY-2010 Full Time = 1 AY
At this point, this employee has worked only 1 consecutive academic year in full-time status because there was a gap (employee did not work at all in AY2008-09). This is where I need a calculation tool to calculate the gap and reset the sum.

At this point the code should move to the next employee in the record set since AY2009-10 would be the last contract for the above employee.

This currently runs from one recordset and creates another recordset which is added to a table.

Any help will be greatly appreciated.

Thanks-
Colleen

 

[MajP]

From your data you show only
sep to dec
jan to may
and
sep to may contracts

do you ever have
Jan to Dec contracts
or multi year contracts starting Jan or ending in Dec
ex
1 Jan 2009 to 31 Dec 2010 : 1Ay
1 Jan 2009 to 31 May 2010: 1.5 AY
1 Sep 2008 to 31 Dec 2010: 2.5 AY

Do you store AY anywhere or is that calculated? If calculated how is it calculated. Pretty simple if you do not allow those cases that I was asking about, but get difficult if you do include.

 

[MADDPM]

do you ever have Jan to Dec contractsor multi year contracts starting Jan or ending in Dec"

No, contracts cover a 2 semester academic year. I use generic dates (instead of fluctuating payroll dates) to track beginnings and endings of contracts.

So basically contracts run:
Fall => Sep-Dec
Spring => Jan-May
Year => Sep-May

I've been able to query and report on most data scenarios - except for this one.

-colleen

 

[MajP]

This is how I did it. See if this works for you.

I added a running sum field in my table "runningAY", and assume you have an employee ID.

First I build this to get my AY

Public Function getAYCredit(dtmStart As Variant, dtmExpire As Variant) As Single
 
 If IsDate(dtmStart) And IsDate(dtmExpire) And (dtmExpire > dtmStart) Then
    getAYCredit = DateDiff("m", dtmStart, dtmExpire)
    If getAYCredit <= 4 Then
      getAYCredit = 0.5
    Else
      getAYCredit = getAYCredit \ 8
    End If
 End If
End Function

Then I built this query

SELECT 
  tblDateGap.ID, 
  tblDateGap.EmpID, 
  tblDateGap.BeginDate, 
  tblDateGap.ExpireDate, 
  tblDateGap.Status, 
  getAYcredit([BeginDate],[ExpireDate]) AS AYcredit,   
  tblDateGap.runningAY
FROM 
  tblDateGap
ORDER BY 
  tblDateGap.EmpID, 
  tblDateGap.BeginDate, 
  tblDateGap.ExpireDate;

Then ran this code for my running sum

Public Sub setRunningAY()
  Dim rs As DAO.Recordset
  Dim empID As Long
  Dim BeginDate As Date
  Dim ExpireDate As Date
  Dim status As String
  Dim ayCredit As Single
  Dim runningAY As Single
  
  Set rs = CurrentDb.OpenRecordset("qryRunningTotal", dbOpenDynaset)
  If Not (rs.EOF And rs.BOF) Then
    empID = rs!empID
    BeginDate = rs!BeginDate
    ExpireDate = rs!ExpireDate
    status = rs!status
    ayCredit = rs!ayCredit
    runningAY = ayCredit
    rs.Edit
      rs!runningAY = ayCredit
    rs.Update
    If Not rs.EOF Then
      rs.MoveNext
    End If
  End If
  Do While Not rs.EOF
    If empID = rs!empID And status = rs!status And DateDiff("M", ExpireDate, rs!BeginDate) <= 4 Then
        runningAY = runningAY + rs!ayCredit
    Else
        runningAY = rs!ayCredit
    End If
    rs.Edit
      rs!runningAY = runningAY
    rs.Update
    empID = rs!empID
    BeginDate = rs!BeginDate
    ExpireDate = rs!ExpireDate
    status = rs!status
    ayCredit = rs!ayCredit
    rs.MoveNext
  Loop
End Sub

This gives me the following results:

EmpID BeginDate ExpireDate Status AYcredit runningAY
1     1-Sep-01 31-May-02 Full Time	1     1
1     1-Sep-02 31-May-03 Full Time	1     2
1     1-Sep-03 31-May-04 Part Time	1     1
1     1-Sep-04 31-Dec-04 Part Time	0.5   1.5
1     1-Jan-05 31-May-05 Part Time	0.5   2
1     1-Sep-05 31-May-07 Full Time	2     2
1     1-Sep-07 31-May-08 Full Time	1     3
1     1-Sep-09 31-May-10 Full Time	1     1
2     1-Sep-01 31-May-02 Full Time	1     1
2     1-Sep-02 31-May-03 Full Time	1     2
2     1-Sep-03 31-May-04 Part Time	1     1
2     1-Sep-04 31-Dec-04 Part Time	0.5   1.5
2     1-Jan-05 31-May-05 Part Time	0.5   2
2     1-Sep-05 31-May-07 Full Time	2     2
2     1-Sep-07 31-May-08 Full Time	1     3
2     1-Sep-09 31-May-10 Full Time	1     1

 

[MADDPM]

Hi MajP,

This worked PERFECTLY for me! You are the best. Thanks so, so much for your time.

-colleen