The below function is working however, it its not deleting the newest record.
Record 1 has a date of 5/17/2001 - I want to delete this date
Record 2 has a date of 10/15/2001 - This is the one that its deleting
Dim strSQL As String
Dim strSQLDel As String
Dim rs As DAO.Recordset
Dim arr() As String
' This query selects the max date, grouping by Constraint Number, only considering records that have duplicates
strSQL = "SELECT [Constraint Number] & '~' & [Allocation Group] AS CompFields, Count([Date Added]) AS RecCount, Max([Date Added]) AS MaxDate FROM [tbl_GMNA Constraint Report Output]" _
& " GROUP BY [Constraint Number] & '~' & [Allocation Group]" _
& " HAVING Count([Date Added]) > 1"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
MsgBox "No dupes found"
'Loop through the recordset deleting duplicates with the same constraint number but lesser dates
Do Until rs.EOF
arr = Split(rs!CompFields, "~")
strSQLDel = "DELETE * FROM [tbl_GMNA Constraint Report Output] WHERE " _
& "[Constraint Number] = '" & arr(0) & "' " _
& "AND [Allocation Group] = '" & arr(1) & "' " _
& "AND [Date Added] < #" & rs!MaxDate & "#"
CurrentDb.Execute strSQLDel, dbFailOnError
Set rs = Nothing
'MsgBox "Duplication Check Completed"