title: MS Access VBA code not deleteing all records
body: <p>I'm trying to delete all the records of one table that appear in another, however it only seems to delete some of the records.</p>
  
  <pre><code>Private Sub removeDuplicates()
  
  Dim resultSet1 As DAO.Recordset
  
  Set resultSet1 = CurrentDb.OpenRecordset("remove")
  resultSet1.MoveFirst
  
  Do Until resultSet1.EOF
  
      Dim sql As String
      sql = "Delete * from [Copy Of remove] Where"
  
      If Not IsNull(resultSet1.Fields(0)) And (resultSet1.Fields(0) &lt;&gt; "") Then
      sql = sql &amp; " PHN = """ &amp; resultSet1.Fields("PHN") &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(1)) And (resultSet1.Fields(1) &lt;&gt; "") Then
      sql = sql &amp; " and Year = " &amp; resultSet1.Fields(1)
      End If
  
      If Not IsNull(resultSet1.Fields(2)) And (resultSet1.Fields(2) &lt;&gt; "") Then
      sql = sql &amp; " and [Date of Referral to Thoracics] = " &amp; resultSet1.Fields(2)
      End If
  
      If Not IsNull(resultSet1.Fields(3)) And (resultSet1.Fields(3) &lt;&gt; "") Then
      sql = sql &amp; " and [Date of Thoracics Consult] = " &amp; resultSet1.Fields(3)
      End If
  
      If Not IsNull(resultSet1.Fields(4)) And (resultSet1.Fields(4) &lt;&gt; "") Then
      sql = sql &amp; " and [Date Thoracic Surgery Booked] = " &amp; resultSet1.Fields(4)
      End If
  
      If Not IsNull(resultSet1.Fields(5)) And (resultSet1.Fields(5) &lt;&gt; "") Then
      sql = sql &amp; " and [Date of Thoracic Surgery] = " &amp; resultSet1.Fields(5)
      End If
  
      If Not IsNull(resultSet1.Fields(6)) And (resultSet1.Fields(6) &lt;&gt; "") Then
      sql = sql &amp; " and [Study Group] = """ &amp; resultSet1.Fields(6) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(7)) And (resultSet1.Fields(7) &lt;&gt; "") Then
      sql = sql &amp; " and [Access Method] = """ &amp; resultSet1.Fields(7) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(8)) And (resultSet1.Fields(8) &lt;&gt; "") Then
      sql = sql &amp; " and Procedure = """ &amp; resultSet1.Fields(8) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(9)) And (resultSet1.Fields(9) &lt;&gt; "") Then
      sql = sql &amp; " and Site = """ &amp; resultSet1.Fields(9) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(10)) And (resultSet1.Fields(10) &lt;&gt; "") Then
      sql = sql &amp; " and [Procedure 2] = """ &amp; resultSet1.Fields(10) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(11)) And (resultSet1.Fields(11) &lt;&gt; "") Then
      sql = sql &amp; " and [Site 2] = """ &amp; resultSet1.Fields(11) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(12)) And (resultSet1.Fields(12) &lt;&gt; "") Then
      sql = sql &amp; " and [Primary site] = """ &amp; resultSet1.Fields(12) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(13)) And (resultSet1.Fields(13) &lt;&gt; "") Then
      sql = sql &amp; " and Grade = """ &amp; resultSet1.Fields(13) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(14)) And (resultSet1.Fields(14) &lt;&gt; "") Then
      sql = sql &amp; " and [T Stage] = """ &amp; resultSet1.Fields(14) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(15)) And (resultSet1.Fields(15) &lt;&gt; "") Then
      sql = sql &amp; " and [N Stage] = """ &amp; resultSet1.Fields(15) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(16)) And (resultSet1.Fields(16) &lt;&gt; "") Then
      sql = sql &amp; " and [M Stage] = """ &amp; resultSet1.Fields(16) &amp; """"
      End If
  
      If Not IsNull(resultSet1.Fields(17)) And (resultSet1.Fields(17) &lt;&gt; "") Then
      sql = sql &amp; " and [Same Staging?] = """ &amp; resultSet1.Fields(17) &amp; """"
      End If
  
      CurrentDb.Execute sql
  
      resultSet1.MoveNext
  Loop
  
  resultSet1.Close
  End Sub
  </code></pre>
  
  <p>This is the code I'm using, to test if it works I've been using the table <em>remove</em> and <em>Copy Of Remove</em> but only about 20 of the 135 records are being deleted even though one is a copy of the other.</p>
  
  <p>Also I match all fields when creating the delete query and I have a feeling this is where the issue is coming from. </p>
  
  <p>P.S.
  Option explicit and option compare database are declared above this sub</p>