Delete duplicate records based on date

Good morning:

I have a table containing assessment date field. I've created a find duplicates query to show me which records are duplicate, but need to come up with a way of automatically deleting records with the oldest assessment dates (i.e., if dates on records is 8/20/2003, 9/21/2004, 10/23/04, I want to delete the records containing 8/20/2003 and 9/21/2004). Any ideas?

Thanks,
Tish

Hi Tish,

The following code will create a temporary delete query that can be run to delete the records found in your duplicate query based on the criteria you wanted.

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim dteDate As Date

dteDate = DMax("[DateField]", "DuplicateQueryName")

strSQL = "Delete * FROM DuplicateQueryName " & _
"WHERE (DuplicateQueryName.DateField)< #" & dteDate & "#"

Set db = CurrentDb
With db
Set qdf = .CreateQueryDef("tmpQuery", strSQL)
DoCmd.OpenQuery "tmpQuery"
.QueryDefs.Delete "tmpQuery"
End With
db.Close
qdf.Close

Just replace with your query name and field name for the date field.

lwells

I've tried to enter the above code, but on runtime, I get a compile error on Dim db As Database. It reads "User-define type not defined." Are there any references that have to be added under "tools" in order for this to run? Thanks again for your help.

Hi Tish,

My apology...add the reference to DAO

Dim db As DAO.Database

That should correct the error.
lwells