none
Delete rows for a given criteria RRS feed

  • 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


    • Edited by Y a h y a Thursday, April 6, 2017 1:33 PM
    Thursday, April 6, 2017 1:32 PM

Answers

  • 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

    Thursday, April 6, 2017 5:53 PM

All replies

  • Hi, Try using rs.Delete Hope it helps... PS. Make sure you have a backup first.
    Thursday, April 6, 2017 1:37 PM
  • 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

    Thursday, April 6, 2017 5:53 PM