Sort first then Copy Cell

[Kaz888]

Please advise how I can write a query that can sort the data in a table into Sequence order first, then copy the value (Date) from the cell above.
The program we are using has a little quirk when dealing with certain entries that leaves the date field blank.

Field Names are: Seq_No and GL_Date

Many thanks.

 

[Norris68]

You will probably need to use a bit of VBA to do this. To make it easier, sort the records in reverse order then iterate through them remembering the date field. If the current field is empty use the one you saved from last time.

e.g.

Public Sub CleanDates()
Const SeqNoFieldName = "Seq_No" 'Substitute your field names here
Const DateFieldName = "GL_Date" 'and here
Const TableName = "MyTable"     'And your table name here

Dim R As ADODB.Recordset
Dim TheDate As Date
Dim GotADate As Boolean

GotADate = False 'Don't do substitutions until we have a date
Set R = New ADODB.Recordset
R.Open "SELECT " & DateFieldName & " FROM " & TableName & " ORDER BY " & SeqNoFieldName & " DESC", CodeProject.Connection, adOpenKeyset, adLockOptimistic
While Not R.EOF
    'If date is not null, store it
    If Not IsNull(R(DateFieldName).Value) Then
        TheDate = R(DateFieldName).Value
        GotADate = True
    End If
    'If we have a date to use
    If GotADate Then
        'And if date is null
        If IsNull(R(DateFieldName).Value) Then
            'Use date we have
            R(DateFieldName).Value = TheDate
            R.Update
        End If
    End If
    R.MoveNext
Wend
R.Close
Set R = Nothing
End Sub