Hi Paul. I can give you a partial solution much like MichaelRed. The output in an Access Form/Report is a problem. My solution is lacking in being able to return a recordset to the recordsource on an Access Form. Here is the code up to that point from an Access Form. This uses a recordset instead of an array to manipulate the data this is more powerful than an array in that you can use all the features on a recordset like sort, filter, indexes, etc... in your manipulation of the data. You could apply this same logic in an ASP and the output would not be a problem. You could, of course, write the ADO recordset back to an Access table and Retrieve the resultset back to the Form's recordsource.
Option Compare Database
Option Explicit
Public recCount As Integer
Public rs1 As New ADODB.Recordset
Private Sub Form_Open(Cancel As Integer)
Dim cn As New ADODB.Connection, Sql1 As String
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
'--- Get recordset from the database
Sql1 = "select * from IDTable order by ID, startdate"
rs.Open Sql1, cn, adOpenStatic, adLockReadOnly
rs.MoveFirst
recCount = rs.RecordCount
'--- Build an internal recordset to manipulate data
Call CreateRecordset(rs)
'-- not able to return the resultset to the recordsource so can't display on Form
''Me.RecordSource = rs1
rs.Close
Set rs = Nothing
End Sub
Public Function CreateRecordset(rs As ADODB.Recordset)
'-- create a fabricated recordset
With rs1.Fields
.Append "ID", adInteger
.Append "startdate", adDBDate
.Append "enddate", adDBDate
.Append "gap", adInteger, adFldIsNullable
.Append "gapNum", adInteger, adFldIsNullable
End With
Dim arrFields As Variant
arrFields = Array("ID", "startdate", "enddate", "gap", "gapNum"
Dim indx As Integer
'''''''''Debug.Print "in Createrecordset "; recCount
rs1.Open
rs.MoveFirst
For indx = 0 To recCount - 1
rs1.AddNew arrFields, Array(rs!ID, rs!startdate, rs!enddate, 0, 0)
rs.MoveNext
Next '- end of recordset
Dim theGap As Integer
rs1.MoveFirst
rs1.MoveNext '-- start with 2nd record
While Not rs1.EOF
theGap = FindRecord(rs1!ID, rs1!startdate, rs1)
rs1!gap = theGap
rs1!gapNum = 1
'''''''Debug.Print "gap update"; rs1!gap; " , "; rs1!gapNum
rs1.Update
rs1.MoveNext
Wend
End Function
Public Function FindRecord(prmID As Integer, prmSdate As Date, rs1 As Recordset) As Integer
''-- Get the prior enddate and subtract and return difference
Dim tempenddate As Date, aGap As Integer
rs1.MovePrevious
tempenddate = rs1!enddate
aGap = DateDiff("d", tempenddate, prmSdate)
rs1.MoveNext
''''Debug.Print "the gap = "; aGap
FindRecord = aGap
End Function
