Need ideas for function to calculate number of days between LAST 2 dates in recordset

[ML!]

I need some creative ideas...

I have a table that tracks project phase changes

tblPhase
PhaseID (pk)
ProjectID (fk to project detail table)
Phase
PhaseDateChange


We track a project as it moves through, typically 6 phases:

1. Initiation
2. Verification
3. Analysis
4. Fulfillment
5. Implementation & Monitoring
6. Complete

Using the PhaseDateChange value, I want to find out how many days it takes for a project to move from one phase to the next. Note projects will not always move in the exact order above. Phases can be skipped or an analysis may require the project, as an example, to step back to verification then forward again to analysis. That's why I want to use the date rather than the phase number.

Obviously DateDiff will work to do the calculation. My challenge is how to determine the dates to use.

I'm thinking of looping through and evaluating the PhaseChangeDates for a particular project and then...

That's where I get stuck. Any help is gratefully accepted.
ML!

 

[WayneRyan]

ML,

The way I see it, you have two options:

One, use a recordset and traverse the data using a recordset. You can use a local variable and
"replace" its value when a later entry is found. You'll be left with the latest date for each phase
at the end of each project. Then you can use the DateDiff function.

Secondly, you can make an initial query. This query will Group by project and phase, selecting
the MAX PhaseDateChange. This query can be used by a subsequent query with the appropriate
DateDiff columns to produce the final result.

hth,
Wayne

 

[ML!]

Helps a lot Wayne. Thanks. Your first option appears most flexible, in case my client wants a different metric down the road. I will report back when I have a working model.

Thanks again,
ML!

 

[WayneRyan]

ML,

Just as a side note, if you have a LOT of data, then the query method will
greatly outperform the RecordSet method ... by a SUBSTANTIAL margin.

Wayne

 

[ML!]

Good reminder Wayne. The issue is that the max date doesn't really work for me by project, by phase yes but not by project.

I have to show number of days between from phase to phase but only on active projects.

What do you think about using a query def to restrict the recordset to active projects then cycling throught the records in date order? Presently, for example there are only 35 active projects.

TIA,
ML

 

[gemma-the-husky]

ML,

Just as a side note, if you have a LOT of data, then the query method will
greatly outperform the RecordSet method ... by a SUBSTANTIAL margin.

Wayne

why?

simply use a recordset based on a query that sorts by date descending

then you only ever need to look at the first and second items anyway

 

[ML!]

Hi Dave,
Actually the title of the thread is now misleading. I now have to calculate the number of days between each of the dates in the recordset.

For project 27 I need to know that there were x number of days in each phase.

So in my report I might end up with:

START DATE: NOV 1 2010
Initiation 1
Verification 2
Analysis 7
Verification 4
Analysis 6
Implementation & monitoring 10
Days to complete: 30
COMPLETE DATE Dec 1 2010

Additional thoughts are most welcome.
ML!

 

[Brianwarnock]

I assume that the length of each phase is the diff between the end of that phase and the end of the previuos, erm.. where do you get the start date from?

Brian

 

[ML!]

Hi Brian,

Yes your assumption is correct, sorry that I didn't explain that very well.

But I capture the start date in the table not the end...

tblPhase
PhaseID (pk)
ProjectID (fk to project detail table)
Phase
PhaseDateChange

...PhaseDateChange is the day that the Phase starts.

So if the project moves to verification on Nov 15, that's the start of the phase. Then say it moves to analysis on Nov 19 then the implementation phase duration is Nov 15, 16, 17 18 (4 days - includes the start date)

But OH NO! As I wrote that I realized that I can't simply use DateDiff because I shouldn't be counting weekend or holiday dates. This just got a little more complicated...didn't it?

ML!

 

[twoplustwo]

Hi ML,

I'd open the series of data in a recordset.

To calculate the difference as specified you'd need to evaluate each weekday in the gap between the date of the current record and the date-1 day in the next record. I'd use a counter to count days that don't equal Saturday/Sunday between these. Then move to the next record and repeat.

This seems a bit OTT so I'd be interested to see a more refined solution.

 

[ML!]

Thanks 2+2. I am trying your idea. I think I understand it...if not I'll let you know!

ML!

 

[ML!]

2+2, I tried to figure out what you meant but couldn't figure out how to make it work. For your interest, I did find a DateDiff calculation here that ignores weekdays...

((DateDiff("d", dte1, dte2) - ((DateDiff("ww", dte1, dte2, 7) + DateDiff("ww", dte1, dte2, 1)))))

Microsoft published a function to address holidays in its MSDN library.

Now I just have to figure out how to determine the dates for the variables...

 

[twoplustwo]

I've figured it out in a recordset but I'm not sure if this is over-complicated

Public Sub CalculateDates()
Dim db As DAO.Database
Dim dtCurrent As Date
Dim dtPrevious As Date
Dim iRecord As Integer
Dim sSql As String
Dim rs As DAO.Recordset
Set db = CurrentDb
 
sSql = "SELECT * FROM tblSample "
sSql = sSql & "ORDER BY JobID ASC"
 
Set rs = db.OpenRecordset(sSql)
 
With rs
   iRecord = 1
   If Not .BOF Then
      Do While Not .EOF
         If iRecord = 1 Then
            dtPrevious = !DateEnd
         Else
            dtCurrent = !DateEnd
            Debug.Print ((DateDiff("d", dtPrevious, dtCurrent) - ((DateDiff("ww", dtPrevious, dtCurrent, 7) + DateDiff("ww", dtPrevious, dtCurrent, 1)))))
            dtPrevious = dtCurrent
         End If
         iRecord = iRecord + 1
         .MoveNext
      Loop
   End If
End With
Set rs = Nothing
 
End Sub

 

[ML!]

Thanks AGAIN! I will try that out later today.