Query that would search multiple criteria and delete

[justinwright]

I'm looking to structure a query that would match ALL of the following critera:

SITE
PAMOUNT
DateAdded
Initial

Then delete it; these are going to be text boxes on a form with a button to search in case of accidental/erraneous entry.

I would use a unique ID to do it, but I've already implemented it and I'd like to just have something that will specifically match multiple critera (see above) and delete that entry.

Any help is much appreciated .


Justin


EDIT:

The only other issue I see is that the DateAdded might be structured 8/11/2010 or like 12/11/2010, depending on if the month is two-digit or not.

 

[pbaldy]

Not clear on what problem you're having. You should be able to add a criteria to all 4 fields.

 

[justinwright]

Well, the idea is how to structure the SQL query in VBA code.

I want to have text boxes for those respective fields, then have a button to click that will execute the query to match ALL of the fields and delete the applicable (will only occur once) record.

 

[pbaldy]

To do it from code (not necessarily the best way by the way), you'd have to concatenate form references into the SQL string:

CurrentDb.Execute "DELETE * FROM TableName WHERE FieldOne = " & Me.ControlOne & " AND FieldTwo = "...

When doing so date values need to be surrounded by #, text value by '. Here's a tutorial on building SQL in VBA:

http://www.baldyweb.com/BuildSQL.htm

 

[justinwright]

What would be the best way, do you think? I assumed that was about the only way short of opening the actual table and manually removing the entry (which I read somewhere that directly opening a back end table was a bad idea).

Thanks for the link though, I'll be looking into that as well.

 

[pbaldy]

Because there's no real change to the structure of the query, it would be more efficient as a saved query. Then in code you'd simply run the saved query.

 

[justinwright]

Sorry for the double post, but from what I took of it it's something like this:

CurrentDb.Execute "Delete * From List WHERE SITE = " & Me.txtSite & " AND PAMOUNT = " & Me.txtAmount & " AND DESC = " & Me.txtDesc""

It searches the List table and deletes the entry that matches all of the inputs... or, at least, that's what it's supposed to do. I haven't tested it quite yet for fear I'm completely brainless. Does that look remotely right?

 

[pbaldy]

It's in the ball park. Make a copy of you database and test without fear!

I'll tell you now that the double quotes at the end will either cause an error or be dropped automatically. Assuming Desc is a text field:

"...AND DESC = '" & Me.txtDesc & "'"

 

[justinwright]

Do I need to apply that to the other fields I'm testing as well (txtSite, txtAmount), or is that just because it's at the end (and to avoid the double ")?

 

[pbaldy]

As I mentioned earlier and the link discusses, date and text fields require delimiters. Numeric fields do not. If those are numeric fields, they are fine as is.

 

[justinwright]

This is a dumb question, I suppose, but when you say "numeric" do you mean numeric as the table datatype (single/double) or numeric as in just a number?

And I see what you mean now, I'll test and post the revised code just in case someone else has a question about something similiar (I can see it being a fairly common use I guess) if I get it right. Thanks for all your help!

 

[pbaldy]

The data type of the field, not necessarily its contents. A field of text data type could hold numbers, but would still require the delimiters.

 

[justinwright]

Just to update everyone, the code I used for the button (in case this can help someone else in the future), is as follows:

    CurrentDb.Execute "Delete * from List WHERE FIELDONE = " & Me.txtText1 & " AND FIELDTWO = " & Me.txtText2 & " "

This takes the txtText1 and txtText2 boxes and goes through the table called "List" and removes them if each both match for FIELDONE and FIELDTWO for their respective boxes.