Delete record based on text found in a field (

Question

I normally do most of this work in Excel 2007, but I do not think excel is the right tool for managing the data that I need to process. So I am trying to convert an excel spreadsheet to an Access 2007 db which I can do with no problem, but before doing anything to the spreadsheet I go through the process of cleaning up the data in it in order to use the resulting information. In excel I use a macro such as the following ``` Sub deletedExceptions_row() Dim i As Long Dim ws As Worksheet On Error GoTo whoa Set ws = Sheets("data") With ws For i = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1 If .Cells(i, 3) = "" Or _ VBA.Left(.Cells(i, 3), 4) = "511-" Or _ VBA.Left(.Cells(i, 3), 5) = "CARL-" Then .Rows(i).Delete End If Next i End With Exit Sub whoa: MsgBox "Value of i is " & i End Sub ``` to remove unnecessary records in the spreadsheet how would I accomplish the same thing in Access 2007. The macro is looking for particular parts or rather the first few characters of the recor…

Accepted Answer

Within Access you can execute a `DELETE` statement to discard rows where the value in a field is an empty string ("") or matches one of your patterns.

```
DELETE FROM YourTable
WHERE
       YourField = ""
    OR YourField ALike "511-%"
    OR YourField ALike "CARL-%";
```

With `YourField` indexed, that pattern matching in the WHERE clause offers a potentially large performance improvement over a query using the `Left()` function such as your spreadsheet macro used. IOW, the following query would require the db engine to run those Left() expressions on every row of `YourTable`. But with the query above and `YourField` indexed, the db engine could simply select the matching rows ... which can easily be an order of magnitude faster.

```
DELETE FROM YourTable
WHERE
       YourField = ""
    OR Left(YourField, 4) = "511-"
    OR Left(YourField, 5) = "CARL-";
```

Delete record based on text found in a field (

Solution

Problem