By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Delete Duplicates with VBA

100+
P: 283
Hello,

I am trying to write a code that will look through a table for duplicates and delete them. I currently have a code I found on an older post in bytes that works fine for deleting duplicates but I am trying to figure out a way i can modify it to delete duplicates based on a criteria.

I have been trying to change it so that if I have strField(1) that is Not Null and another strField(2) that Is Null and strField(1) is a duplicate then delete it.

Any help would be appreciated.

Thanks,

Slen

below is the code i am working with (orginally posted by NeoPa)
Expand|Select|Wrap|Line Numbers
  1. 'DelDups Removes duplicate records in strTable matched on strField 
  2. Public Sub DelDups(strTable As String, strField As String) 
  3.     Dim strSQL As String, varLastVal As Variant 
  4.  
  5.     'Recordset must be full table but sorted by the field we're checking 
  6.     strSQL = "SELECT * FROM [" & strTable & "] ORDER BY [" & strField & "]" 
  7.     With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) 
  8.         varLastVal = Null 
  9.         'For each record, check against previous value in strField 
  10.         'If same then this is a duplicate so can be removed 
  11.         Do Until .EOF 
  12.             If .Fields(strField) = varLastVal Then 
  13.                 Call .Delete 
  14.             Else 
  15.                 varLastVal = .Fields(strField) 
  16.             End If 
  17.             Call .MoveNext 
  18.         Loop 
  19.     'Ending the 'With' releases the Recordset 
  20.     End With 
  21. End Sub 
  22.  
  23.  
Apr 25 '12 #1

✓ answered by Rabbit

Well, then you pretty much translate what you said into VBA code
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(.Fields(strField1) And IsNull(.Fields(strField2) Then

Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Why do it through VBA instead of a delete query?
Apr 25 '12 #2

100+
P: 283
Mostly because I have code built that is doing a few other things to the table before and after finding the duplicates.

I was trying to avoid the whole dup/append queries and then pull that information back in to the main table.

slen :D
Apr 25 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
Well, then you pretty much translate what you said into VBA code
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(.Fields(strField1) And IsNull(.Fields(strField2) Then
Apr 25 '12 #4

100+
P: 283
Thanks Rabbit,

That was what I needed!

I am still finding that it is not quite deleting all the duplicates which I think its because there might be some that could be triplicates.

I will continue to play with it and see if i can figure it out. I apprecaite the help in getting me in the right direction.

Thanks again,

Slen :D
Apr 25 '12 #5

ADezii
Expert 5K+
P: 8,597
The following Demo, hard coded for the sake of simplicity, will Delete any Duplicates, Triplicates, etc. where Duplication is defined as [Field1] NOT being NULL and [Field2] Is NULL. I also posted sample Before and after Data.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rst = MyDB.OpenRecordset("Table1", dbOpenDynaset)
  6.  
  7. With rst
  8.   Do While Not .EOF
  9.     If Not IsNull(![Field1]) And IsNull(![Field2]) Then     'Check for Duplication
  10.       If DCount("*", "Table1", "[Field1] = '" & ![Field1] & "' AND [Field2] Is Null") > 1 Then
  11.         .Delete
  12.       End If
  13.     End If
  14.       .MoveNext
  15.   Loop
  16. End With
  17.  
  18. rst.Close
  19. Set rst = Nothing
Before Executing Code:
Expand|Select|Wrap|Line Numbers
  1. PK  Field1  Field2
  2. 1     A       1
  3. 2     B
  4. 3     C
  5. 4     D       4
  6. 5     E       5
  7. 6     f       6
  8. 7     G       7
  9. 8     H       8
  10. 9     B
  11. 10    C
  12. 11    E
  13. 12    f
  14. 13    G
  15. 14    t      44
  16. 15    M
  17. 16    B
  18. 17    B
  19. 18    C
  20. 19    D
  21. 20    p
After Code Execution:
Expand|Select|Wrap|Line Numbers
  1. PK  Field1  Field2
  2. 1     A       1
  3. 4     D       4
  4. 5     E       5
  5. 6     f       6
  6. 7     G       7
  7. 8     H       8
  8. 11    E
  9. 12    f
  10. 13    G
  11. 14    t      44
  12. 15    M
  13. 17    B
  14. 18    C
  15. 19    D
  16. 20    p
P.S. - Records with Primary Key Values of 2, 3, 9, 10, and 16 have been Deleted.
Apr 25 '12 #6

100+
P: 283
Hi ADezii,

Once again you skills are the stuff of legend. That worked a lot better. Now I just have to figure out how i can change it some to go in to a function :D

Really appreciate your help!

Is there anyway i can change your response to the correct answer?

Slen
Apr 26 '12 #7

100+
P: 283
An add on question for you,

How would i modify this to check for duplicates and then if there is one value in the duplicate that is different to delete it?

I tried something like this but i keep getting errors...
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rst = MyDB.OpenRecordset("tbl", dbOpenDynaset)
  6.  
  7. With rst
  8.   Do While Not .EOF
  9.      If Not IsNull(![strField1]) And IsNull(![strField2]) Then     'Check for Duplication
  10.       If DCount("*", "tbl", "[strField1] = '" & ![strField1] & "' AND [strField2] Is Null") > 1 And _
  11.         DLookup("*", "tbl", "[Code1]= 'No Error'") Then
  12.          .Delete
  13.       End If
  14.      'End If
  15.     End If
  16.       .MoveNext
  17.   Loop
  18. End With
  19.  
  20. rst.Close
  21. Set rst = Nothing
  22.  
Apr 26 '12 #8

ADezii
Expert 5K+
P: 8,597
if there is one value in the duplicate that is different to delete it?
Kindly explain, not sure what you mean...
Apr 26 '12 #9

100+
P: 283
Going off of the code you did if I wanted to make adjustments to it so if I have a duplicate record the identifier would be strField1 (an ID #), in strField2 one record is Null and the other is Not Null and in a strField3 I could have two different values, for example code1 and code2. If I wanted to delete only the record where strField1 is a duplicate, strField2 is Null and strField3 = code2, how could I adjust this??

I can get this to run but it is not deleting anything...any ideas?

Expand|Select|Wrap|Line Numbers
  1. With rst
  2.   Do While Not .EOF
  3.      If Not IsNull(![ID]) And IsNull(![Amt]) Then    'Check for Duplication
  4.       If DCount("*", "tblName", "[ID] = '" & ![ID] & "' >1 And [Amt] Is Null And [ErrCode] = '" & strText & "'") Then
  5.          .Delete
  6.       End If
  7.      'End If
  8.     End If
  9.       .MoveNext
  10.   Loop
  11. End With
  12.  
  13. rst.Close
  14. Set rst = Nothing
  15.  
Apr 26 '12 #10

ADezii
Expert 5K+
P: 8,597
Try:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim strCode As String
  4.  
  5. strCode = "YaDa"
  6.  
  7. Set MyDB = CurrentDb
  8. Set rst = MyDB.OpenRecordset("Table1", dbOpenDynaset)
  9.  
  10. With rst
  11.   Do While Not .EOF
  12.     If Not IsNull(![Field1]) And IsNull(![Field2]) Then     'Check for Duplication
  13.       If DCount("*", "Table1", "[Field1] = '" & ![Field1] & "' AND [Field2] Is Null AND " & _
  14.          "[ErrCode] = '" & strCode & "'") > 1 Then
  15.         .Delete
  16.       End If
  17.     End If
  18.       .MoveNext
  19.   Loop
  20. End With
  21.  
  22. rst.Close
  23. Set rst = Nothing
Apr 27 '12 #11

100+
P: 283
Hi ADezii,

Apprecaite your continued help with this.

I tried your revision but it did not work. I made a small adjustment where I took out the >1 and for some reason that worked...any idea why?

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database 
  2. Dim rst As DAO.Recordset 
  3. Dim strCode As String 
  4.  
  5. strCode = "YaDa" 
  6.  
  7. Set MyDB = CurrentDb 
  8. Set rst = MyDB.OpenRecordset("Table1", dbOpenDynaset) 
  9.  
  10. With rst 
  11.   Do While Not .EOF 
  12.     If Not IsNull(![Field1]) And IsNull(![Field2]) Then     'Check for Duplication 
  13.       If DCount("*", "Table1", "[Field1] = '" & ![Field1] & "' AND [Field2] Is Null AND " & _ 
  14.          "[ErrCode] = '" & strCode & "'")  Then 
  15.         .Delete 
  16.       End If 
  17.     End If 
  18.       .MoveNext 
  19.   Loop 
  20. End With 
  21.  
rst.Close
Set rst = Nothing
Apr 27 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.