Using expression builder to write a delete query

[presence76]

I am using expression builder to write a delete query. I have a table that has a column called batch. In this column are data that look like:


ED20040524I1002
BA20040524DBT1

and so on.

I want to delete anything that does not begin with BA.
Therefore, my query reads

NOT LIKE "BA*"

This does not work and I cannot fiqure out why. I have played with it, trying other values on other fields in the same table and they work fine but not in this field. Any ideas??

 

[Cbrine]

I'm not sure why your query isn't working, but as an alternate way of doing it, try this.

Create a custom field in your query.
Test:Left(batch,2)

Criteria = "BA"

 

[khexcel]

Try typing:
not like "BA*"
in the criteria section of the query builder section.
Your SQL should read something like this:

DELETE Table1.Your_field
FROM Table1
WHERE (((Table1.Your_field) Not Like "BA*"));

 

[presence76]

What I ended up doing was making the query a select query instead of a delete query. For some reason, ACCESS does not like double negatives.

 

[mdmilner]

Here's just a general suggestion, especially when using the QBE Wizard to do things like UPDATE/DELETE queries.

UPDATE and DELETE perform actions on your data. If you're testing, and run them, it will irrevocably change your data. If you delete something you need -- and you can't get it back -- well, it's obvious that's bad.

Always start by building your queries as SELECT queries. Make them work that way first - and look at what shows up. That includes setting your parameters. Only after you have it working (AND SAVED once) do you change the query to a different type, such as your above referenced DELETE query.

Cbrine was correct though. It should have accepted Not Like in the parameter. Sometimes the best thing to do is start the query over. Delete and recreate.

Mike