ADO + Open Table
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.
|