CODEWITHSUNDEEP
mysqlvbams-access
TCulos
TCulos

Reputation: 183

MS Access VBA code not deleteing all records

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.

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) <> "") Then
    sql = sql & " PHN = """ & resultSet1.Fields("PHN") & """"
    End If

    If Not IsNull(resultSet1.Fields(1)) And (resultSet1.Fields(1) <> "") Then
    sql = sql & " and Year = " & resultSet1.Fields(1)
    End If

    If Not IsNull(resultSet1.Fields(2)) And (resultSet1.Fields(2) <> "") Then
    sql = sql & " and [Date of Referral to Thoracics] = " & resultSet1.Fields(2)
    End If

    If Not IsNull(resultSet1.Fields(3)) And (resultSet1.Fields(3) <> "") Then
    sql = sql & " and [Date of Thoracics Consult] = " & resultSet1.Fields(3)
    End If

    If Not IsNull(resultSet1.Fields(4)) And (resultSet1.Fields(4) <> "") Then
    sql = sql & " and [Date Thoracic Surgery Booked] = " & resultSet1.Fields(4)
    End If

    If Not IsNull(resultSet1.Fields(5)) And (resultSet1.Fields(5) <> "") Then
    sql = sql & " and [Date of Thoracic Surgery] = " & resultSet1.Fields(5)
    End If

    If Not IsNull(resultSet1.Fields(6)) And (resultSet1.Fields(6) <> "") Then
    sql = sql & " and [Study Group] = """ & resultSet1.Fields(6) & """"
    End If

    If Not IsNull(resultSet1.Fields(7)) And (resultSet1.Fields(7) <> "") Then
    sql = sql & " and [Access Method] = """ & resultSet1.Fields(7) & """"
    End If

    If Not IsNull(resultSet1.Fields(8)) And (resultSet1.Fields(8) <> "") Then
    sql = sql & " and Procedure = """ & resultSet1.Fields(8) & """"
    End If

    If Not IsNull(resultSet1.Fields(9)) And (resultSet1.Fields(9) <> "") Then
    sql = sql & " and Site = """ & resultSet1.Fields(9) & """"
    End If

    If Not IsNull(resultSet1.Fields(10)) And (resultSet1.Fields(10) <> "") Then
    sql = sql & " and [Procedure 2] = """ & resultSet1.Fields(10) & """"
    End If

    If Not IsNull(resultSet1.Fields(11)) And (resultSet1.Fields(11) <> "") Then
    sql = sql & " and [Site 2] = """ & resultSet1.Fields(11) & """"
    End If

    If Not IsNull(resultSet1.Fields(12)) And (resultSet1.Fields(12) <> "") Then
    sql = sql & " and [Primary site] = """ & resultSet1.Fields(12) & """"
    End If

    If Not IsNull(resultSet1.Fields(13)) And (resultSet1.Fields(13) <> "") Then
    sql = sql & " and Grade = """ & resultSet1.Fields(13) & """"
    End If

    If Not IsNull(resultSet1.Fields(14)) And (resultSet1.Fields(14) <> "") Then
    sql = sql & " and [T Stage] = """ & resultSet1.Fields(14) & """"
    End If

    If Not IsNull(resultSet1.Fields(15)) And (resultSet1.Fields(15) <> "") Then
    sql = sql & " and [N Stage] = """ & resultSet1.Fields(15) & """"
    End If

    If Not IsNull(resultSet1.Fields(16)) And (resultSet1.Fields(16) <> "") Then
    sql = sql & " and [M Stage] = """ & resultSet1.Fields(16) & """"
    End If

    If Not IsNull(resultSet1.Fields(17)) And (resultSet1.Fields(17) <> "") Then
    sql = sql & " and [Same Staging?] = """ & resultSet1.Fields(17) & """"
    End If

    CurrentDb.Execute sql

    resultSet1.MoveNext
Loop

resultSet1.Close
End Sub

This is the code I'm using, to test if it works I've been using the table remove and Copy Of Remove but only about 20 of the 135 records are being deleted even though one is a copy of the other.

Also I match all fields when creating the delete query and I have a feeling this is where the issue is coming from.

P.S. Option explicit and option compare database are declared above this sub

Upvotes: 0

Views: 58

Answers (1)

Marc B
Marc B

Reputation: 360672

You haven't quoted your date value, so you're doing a division operation:

DELETE ... WHERE [Date...] = 6/5/2013
which becomes
DELETE ... WHERE [Date...] = 0.0005961etc...

Try

DELETE ... WHERE [Date...] = #6/5/2013#
                             ^--------^

instead.

Upvotes: 1

Related Questions