mato01
asked on
Delete based on Column, and date added
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
453 9/01/2011
453 9/30/2011
For example it would keep the one with the 9/30/2011 date
Contraint Number Date
453 9/01/2011
453 9/30/2011
Option Compare Database
Function DeleteDuplicates()
Dim DeleteCount As Double ' Make this a Long if you really have that many records
Dim temp
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!"
Exit Function
End If
'Stop
rst.MoveFirst
Do Until rst.EOF
If temp = rst![Constraint Number] Then
rst.Delete
DeleteCount = DeleteCount + 1
Else
temp = rst![Constraint Number]
End If
rst.MoveNext
Loop
Set rst = Nothing
MsgBox "Found and deleted " & CStr(DeleteCount) & " records."
End Function
ASKER
It gave an error "Compile Error: Method or data member not found"
at below line
rst2.Open sql2
at below line
rst2.Open sql2
change fldDate to the name of the field that holds the date in your db and also change rst2.Count to rst2.RecordCount
ASKER
Make above suggested changes, and still "Compile Error: Method or data member not found" at
rst2.Open sql2. Did not work. Also, tried to change rst2.Open sql2 to rst2.OpenRecordset sql2.
************************** ********** ********** ********** ********** ********** ********** ********** ********** ********** ***
rst2.Open sql2
datMax = rst2("Date Added")
If rst2.RecordCount > 1 Then
rst2.Open sql2. Did not work. Also, tried to change rst2.Open sql2 to rst2.OpenRecordset sql2.
**************************
rst2.Open sql2
datMax = rst2("Date Added")
If rst2.RecordCount > 1 Then
try:
sql2="SELECT [tbl_GMNA Constraint Report Output].* FROM [tbl_GMNA Constraint Report Output]"
sql2=sql2 & " WHERE [Constraint Number]=" & rst.fields("Constraint Number")
sql2=sql2 & " ORDER BY [tbl_GMNA Constraint Report Output].[Constraint Number] DESC"
rst2.open sql2
datMax=rst2.fields("fldDat e")
My apology for the syntax errors.
If you still have problem do a debug.print and post the output. That is
sql2="SELECT [tbl_GMNA Constraint Report Output].* FROM [tbl_GMNA Constraint Report Output]"
sql2=sql2 & " WHERE [Constraint Number]=" & rst.fields("Constraint Number")
sql2=sql2 & " ORDER BY [tbl_GMNA Constraint Report Output].[Constraint Number] DESC"
Debug.print sql2
The output will be in the immediate window. Copy and post
sql2="SELECT [tbl_GMNA Constraint Report Output].* FROM [tbl_GMNA Constraint Report Output]"
sql2=sql2 & " WHERE [Constraint Number]=" & rst.fields("Constraint Number")
sql2=sql2 & " ORDER BY [tbl_GMNA Constraint Report Output].[Constraint Number] DESC"
rst2.open sql2
datMax=rst2.fields("fldDat
My apology for the syntax errors.
If you still have problem do a debug.print and post the output. That is
sql2="SELECT [tbl_GMNA Constraint Report Output].* FROM [tbl_GMNA Constraint Report Output]"
sql2=sql2 & " WHERE [Constraint Number]=" & rst.fields("Constraint Number")
sql2=sql2 & " ORDER BY [tbl_GMNA Constraint Report Output].[Constraint Number] DESC"
Debug.print sql2
The output will be in the immediate window. Copy and post
ASKER
That didn't work. This is new for me. How do I debug.print?
ASKER
I've attached a sample of the database for reference.
The error message is
COMPILE ERROR: Method or Data Member Not Found highlights this line.
rst2.Open sql2
test11.accdb
The error message is
COMPILE ERROR: Method or Data Member Not Found highlights this line.
rst2.Open sql2
test11.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I ran it, I received the following error.
Run-time error '3075.:
Invalid use of IS operator in query expression '[Constraint Number] = 766 AND [Date Added] Is not #9/11/2011'.
Run-time error '3075.:
Invalid use of IS operator in query expression '[Constraint Number] = 766 AND [Date Added] Is not #9/11/2011'.
ASKER
Changed
sql2 = sql2 & " AND [Date Added] Is Not #" & datMax & "#"
to
sql2 = sql2 & " AND [Date Added] <> #" & datMax & "#"
and received a Run-time error '3464'
Data type mismatch in criteria expression
sql2 = sql2 & " AND [Date Added] Is Not #" & datMax & "#"
to
sql2 = sql2 & " AND [Date Added] <> #" & datMax & "#"
and received a Run-time error '3464'
Data type mismatch in criteria expression
ASKER
I was able to get an answer. But, wanted to award you ALL the points for all your great help. Thanks
Open in new window