I have a table that is sorted ASC by three columns: CPT4, MOD and EFFDATE in that order. So, of course it sorts by CPT4 first, MOD second and EFFDATE third.

There are multiple rows for each CPT4 and there are different dates for each CPT4. I am going through the table one record at a time and adding an entry in the 'flag' column to tell if the record does not have the most current date for the CPT4.

Problem: When the ADO recordset is navigating through the table it does not keep my sort that I did in the table. This is a problem because my whole code depends upon these three columns being sorted the way they are.

Here is my function that I am using:

Function SortOutVANTAG05()
    Dim rst As New ADODB.Recordset

    Dim First As Date
    Dim Second As Date

    rst.Open "SELECT * FROM VANTAG05", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

    Second = #1/1/2005#

    Do Until rst.EOF

        First = rst!EFFDATE

        If First > Second Then

            rst.MovePrevious
            rst!flag = "Not the Current One"
            rst.MoveNext

        End If

        Second = First

        rst.MoveNext

    Loop

End Function

Thanks for the help.

Just change your SQL so that the recordset is sorted in the way you want:
SELECT * FROM VANTAG05 ORDER BY CPT4, MOD, EFFDATE

Perfect.

Thanks a lot.