The attached module works quickly, but, what I need is to put in one more criteria, where it deletes the oldest, and leaves the newest.
For example it would keep the one with the 9/30/2011 date
Contraint Number Date
Option Compare Database
Dim DeleteCount As Double ' Make this a Long if you really have that many records
Dim sql As String
Dim rst As Recordset
'' Change DESC to ASC to keep the older record
'' Leave DESC to keep the newer record
sql = "SELECT [tbl_GMNA Constraint Report Output].* FROM [tbl_GMNA Constraint Report Output] ORDER BY [tbl_GMNA Constraint Report Output].[Constraint Number] DESC"
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
If rst.EOF Then
MsgBox "There are no records in this table!"
Do Until rst.EOF
If temp = rst![Constraint Number] Then
DeleteCount = DeleteCount + 1
temp = rst![Constraint Number]
Set rst = Nothing
MsgBox "Found and deleted " & CStr(DeleteCount) & " records."