Macro to Delete Records matching certain conditions in Query

[ban1k]

Greetings-

I have created a join of multiple tables to create 'all possible combinations' in a query (which I then concatenated in a query function). I have gotten that far, where I need help though is in creating a macro to delete records from the query matching certain criteria. There is an IIF function denoting 'Remove' for those items to be deleted.

So it looks something like:
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]AllPossible
[/TD]
[TD]Remove?
[/TD]
[/TR]
[TR]
[TD]WTL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WTL-ABC
[/TD]
[TD]Remove
[/TD]
[/TR]
[TR]
[TD]WTL-LP
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WTL-ABC-Q
[/TD]
[TD]Remove
[/TD]
[/TR]
</TBODY>[/TABLE]

In this case, basically the rule is to delete any record containing both "WTL" and "-ABC".

Appreciate any insight (or basics I am overlooking here), as I am newer at this.
Thanks!

 

[brian.wethington]

Since you have a helper column with remove in it you could perhaps use:

Sub Removal()
If WorksheetFunction.CountIf(Range("B:B"), "Remove") > 0 Then
    Range("A2", Cells(Rows.Count, 1).End(xlUp).Offset(, 1)).AutoFilter Field:=2, Criteria1:="Remove"
    Range("A2", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Range("A2").AutoFilter
End If
End Sub

 

[ban1k]

Hi Brian - Thanks for the quick reply - I apologize for not making it clear in the original message, but actually I was trying to do this via MS Access, due to the likely large amount of records to come. (I agree, the VB code above certainly would do the trick in Excel.)
Would you happen to know how to translate something similar for the situation in Access? Or maybe I just need help being pointed in the right direction with the syntax for using Macro builder in Access.

Thanks again~

 

[brian.wethington]

Ah, that is my fault, I did not realize I got into the access forum. It has been a while since I have messed around with Access but I will check when I am at work tomorrow; it doesn't seem like too difficult of a request.

 

[alansidman]

Have you considered a delete query?

[COLOR=#444444][FONT=courier new]DELETE FROM [/FONT][/COLOR][I]table_name[/I]
[COLOR=#444444][FONT=courier new]WHERE [/FONT][/COLOR][I]some_column[/I][COLOR=#444444][FONT=courier new]=[/FONT][/COLOR][I]some_value[/I][COLOR=#444444][FONT=courier new];


[/FONT][/COLOR]

 

[ban1k]

Thanks Brian & Alan, for chipping in on this.

Alan - after some brushing up on my syntax and toying around, I took a step back and removed the sort of 'helper column/field' I had above, and went with a delete query as you noted.

Based on what I stated above, this pretty much translated to:

DELETE AllPossible.AllPossible</SPAN>
FROM AllPossible</SPAN>
WHERE (([AllPossible].[AllPossible] Like "*WTL*" And [AllPossible].[AllPossible] Like "*-ABC*"));

Thank you very much for the help!</SPAN></SPAN>