Delete rows for a given criteria

Question

Hi

I am using below code to find columns in a table that have dates less than #1/1/1753#. How can I also delete these rows so at the end of process no row is left that has a date field with value less than #1/1/1753#?

Thanks

Regards

  Set rs = CurrentDb.OpenRecordset(Tbl)
  
  rs.MoveLast

  While Not rs.BOF
    St1 = ""
    For I = 0 To rs.Fields.Count - 1
        If (rs.Fields(I).Type = dbDate) Then
          If rs.Fields(I).Value < #1/1/1753# Then
            St1 = St1 & " Field: '" & rs.Fields(I).Name & "' " & rs.Fields(I).Value & " too low" & vbCRLF
          End If
        End If
      End If
    Next

    Print #1, St1

    rs.MovePrevious
  Wend

Answer 1

Hi, Try using rs.Delete Hope it helps... PS. Make sure you have a backup first.

Answer 2

Try this function:

Function DeleteRows(TableName As String, OperativeDate As Date)

    Dim n As Integer
    Dim strSQL As String
    
    For n = 0 To CurrentDb.TableDefs(TableName).Fields.Count - 1
        If CurrentDb.TableDefs(TableName).Fields(n).Type = dbDate Then
            strSQL = strSQL & "OR [" & CurrentDb.TableDefs(TableName).Fields(n).Name & _
                "] < #" & Format(OperativeDate, "yyyy-mm-dd") & "# "
        End If
    Next n
    
    strSQL = "DELETE * FROM [" & TableName & "] WHERE " & Mid(strSQL, 4)
    CurrentDb.Execute strSQL, dbFailOnError
    
End Function

Call it like this:

DeleteRows "NameOfYourTable",#1753-01-01#

I assume that your date derives from the adoptian of the Gregorian calendar by Britain and the American colonies in 1752.  As the dates removed were 3 to 13 September 1752 any dates after 13 September 1752 will be valid, rather than just those from the start of 1753.

---

Ken Sheridan, Stafford, England