Filter on a column for a list of keywords 1

[CJSwed]

Hey Guys...
I have a bunch of IDs to delete from a table that contains about 5,000 records. These ID's are in a column called contents and I am trying to filter the table to show just the records that have one of these ids in the contents column so that I can delete the ids from the column.

I came up with a code below which should work but when I got to run it I get a this query is to complex. Any ideas?

Thanks!

SELECT *
FROM TableA
WHERE (((TableA.Contents) Like "**1150-CAR**" Or (TableA.Contents) Like "**1150-CASE**" Or (TableA.Contents) Like "**1150-CHARGER**" Or (TableA.Contents) Like "**1150-HEADSET**" Or (TableA.Contents) Like "**1150-HOLDER**" Or (TableA.Contents) Like "**1150-HOLSTER**" Or (TableA.Contents) Like "**1150-original-battery**" Or (TableA.Contents) Like "**1150-original-HEADSET**" Or (TableA.Contents) Like "**1200-HEADSET**" Or (TableA.Contents) Like "**1200-HOLDER**" Or (TableA.Contents) Like "**1200-HOLSTER**" Or (TableA.Contents) Like "**4NE1-CAR**" Or (TableA.Contents) Like "**510-CAR**" Or (TableA.Contents) Like "**5350-CAR**" Or (TableA.Contents) Like "**5350-CASE**" Or (TableA.Contents) Like "**5350-CHARGER**" Or (TableA.Contents) Like "**5350-HEADSET**" Or (TableA.Contents) Like "**5350-HOLDER**" Or (TableA.Contents) Like "**5350-HOLSTER**" Or (TableA.Contents) Like "**ACBATCHARLGC**" Or (TableA.Contents) Like "**AUDHANAUDHAN21**" Or (TableA.Contents) Like "**AUDHANAUDHAN23**" Or (TableA.Contents) Like "**AUDHANAUDHAN24**" Or (TableA.Contents) Like "**AUDRAPBATCHA1**" Or (TableA.Contents) Like "**AUDRAPBATCHA112**" Or (TableA.Contents) Like "**AUDRAPBATCHA34**" Or (TableA.Contents) Like "**c1300-BATTERIES**" Or (TableA.Contents) Like "**c1300-battery**" Or (TableA.Contents) Like "**c1300-CAR**" Or (TableA.Contents) Like "**c1300-CASE**" Or (TableA.Contents) Like "**c1300-CHARGER**" Or (TableA.Contents) Like "**c1300-earbud**" Or (TableA.Contents) Like "**c1300-HEADSET**" Or (TableA.Contents) Like "**c1300-HOLDER**" Or (TableA.Contents) Like "**c1300-HOLSTER**" Or (TableA.Contents) Like "**c1300-HOME**" Or (TableA.Contents) Like "**C1500-CAR**" Or (TableA.Contents) Like "**C1500-CHARGER**" Or (TableA.Contents) Like "**C1500-HEADSET**" Or (TableA.Contents) Like "**C1500-HOLDER**" Or (TableA.Contents) Like "**C1500-HOLSTER**" Or (TableA.Contents) Like "**C1500-HOME**" Or (TableA.Contents) Like "**C1500-original-HEADSET**" Or (TableA.Contents) Like "**C1500-pouch**" Or (TableA.Contents) Like "**C2000-BATTERIES**" Or (TableA.Contents) Like "**C2000-CAR**" Or (TableA.Contents) Like "**C2000-CASE**" Or (TableA.Contents) Like "**C2000-CHARGER**" Or (TableA.Contents) Like "**C2000-HEADSET**" Or (TableA.Contents) Like "**C2000-HOLDER**" Or (TableA.Contents) Like "**C2000-HOLSTER**" Or (TableA.Contents) Like "**C2000-HOME**" Or (TableA.Contents) Like "**C2000-original-battery**" Or (TableA.Contents) Like "**C2000-original-HEADSET**" Or (TableA.Contents) Like "**c2000-pouch**" Or (TableA.Contents) Like "**CG225-BATTERIES**" Or (TableA.Contents) Like "**CG225-CAR**" Or (TableA.Contents) Like "**CG225-CASE**" Or (TableA.Contents) Like "**CG225-cases**" Or (TableA.Contents) Like "**CG225-CHARGER**" Or (TableA.Contents) Like "**CG225-HEADSET**" Or (TableA.Contents) Like "**CG225-HOLDER**" Or (TableA.Contents) Like "**CG225-HOLSTER**" Or (TableA.Contents) Like "**CG225-HOME**" Or (TableA.Contents) Like "**CG225-original-battery**" Or (TableA.Contents) Like "**CG225-original-HEADSET**" Or (TableA.Contents) Like "**CG225-pouch**" Or (TableA.Contents) Like "**CG300-BATTERIES**" Or (TableA.Contents) Like "**CG300-CAR**" Or (TableA.Contents) Like "**CG300-CHARGER**" Or (TableA.Contents) Like "**CG300-HEADSET**" Or (TableA.Contents) Like "**CG300-HOME**" Or (TableA.Contents) Like "**CG300-original-battery**" Or (TableA.Contents) Like "**CG300-original-HEADSET**" Or (TableA.Contents) Like "**cg300-pouch**" Or (TableA.Contents) Like "**CLC-30W**" Or (TableA.Contents) Like "**g4010-BATTERIES**" Or (TableA.Contents) Like "**g4010-battery**" Or (TableA.Contents) Like "**g4010-CAR**" Or (TableA.Contents) Like "**g4010-case**" Or (TableA.Contents) Like "**g4010-chargers**" Or (TableA.Contents) Like "**g4010-HEADSET**" Or (TableA.Contents) Like "**g4010-HOLDER**" Or (TableA.Contents) Like "**g4010-HOLSTER**" Or (TableA.Contents) Like "**g4010-home**" Or (TableA.Contents) Like "**g4050-BATTERIES**" Or (TableA.Contents) Like "**g4050-battery**" Or (TableA.Contents) Like "**g4050-CAR**" Or (TableA.Contents) Like "**g4050-case**" Or (TableA.Contents) Like "**g4050-chargers**" Or (TableA.Contents) Like "**g4050-HEADSET**" Or (TableA.Contents) Like "**g4050-HOLDER**" Or (TableA.Contents) Like "**g4050-HOLSTER**" Or (TableA.Contents) Like "**g4050-home**" Or (TableA.Contents) Like "**GENLEATCASLU**" Or (TableA.Contents) Like "**HFLG33023OEM**" Or (TableA.Contents) Like "**HOLCLIPCARMO**" Or (TableA.Contents) Like "**L1400-CAR**" Or (TableA.Contents) Like "**L1400-CASE**" Or (TableA.Contents) Like "**L1400-CHARGER**" Or (TableA.Contents) Like "**l1400-earbud**" Or (TableA.Contents) Like "**L1400-HEADSET**" Or (TableA.Contents) Like "**L1400-HOLDER**" Or (TableA.Contents) Like "**L1400-HOLSTER**" Or (TableA.Contents) Like "**L1400-HOME**" Or (TableA.Contents) Like "**L3BLITIONBAT**" Or (TableA.Contents) Like "**LBOEMLIIONBA**" Or (TableA.Contents) Like "**lg-1150**" Or (TableA.Contents) Like "**LG4NE1**" Or (TableA.Contents) Like "**LG4NE11**" Or (TableA.Contents) Like "**LG4NE12**" Or (TableA.Contents) Like "**LG4NE1BATTERY**" Or (TableA.Contents) Like "**LG4NE1CASE**" Or (TableA.Contents) Like "**LG4NE1CHARGERS**" Or (TableA.Contents) Like "**LG4NE1HEADSETS**" Or (TableA.Contents) Like "**LG4NE1HOLSTER**" Or (TableA.Contents) Like "**LG4NE1HOLSTERS**" Or (TableA.Contents) Like "**LG4NE1-ORIGINAL**" Or (TableA.Contents) Like "**LG510**" Or (TableA.Contents) Like "**LG510HOLSTER**" Or (TableA.Contents) Like "**lg-c1300**" Or (TableA.Contents) Like "**lg-C1500**" Or (TableA.Contents) Like "**lg-C2000**" Or (TableA.Contents) Like "**lg-CG225**" Or (TableA.Contents) Like "**lg-cg300**" Or (TableA.Contents) Like "**LGDESKTOP**" Or (TableA.Contents) Like "**lg-g4010**" Or (TableA.Contents) Like "**lg-g4050**" Or (TableA.Contents) Like "**LGICLGLGIC25**" Or (TableA.Contents) Like "**lg-L1400**" Or (TableA.Contents) Like "**LGLGICDMSPV1**" Or (TableA.Contents) Like "**LGLUCGOLDMSP**" Or (TableA.Contents) Like "**LG-LX1200**" Or (TableA.Contents) Like "**LG-LX5350**" Or (TableA.Contents) Like "**LGVX10AC**" Or (TableA.Contents) Like "**LGVX10CAR**" Or (TableA.Contents) Like "**LGVX10CASE**" Or (TableA.Contents) Like "**LGVX10HEADSETS**" Or (TableA.Contents) Like "**LGVX10HOLSTER**" Or (TableA.Contents) Like "**LGVX10HOLSTERS**" Or (TableA.Contents) Like "**LGVX1AC**" Or (TableA.Contents) Like "**LGVX1CAR**" Or (TableA.Contents) Like "**LGVX1CHARGERS**" Or (TableA.Contents) Like "**LGVX1HEADSETS**" Or (TableA.Contents) Like "**LGVX1HOLSTER**" Or (TableA.Contents) Like "**LGVX1HOLSTERS**" Or (TableA.Contents) Like "**LGVX1LEAT**" Or (TableA.Contents) Like "**LG-vx3200**" Or (TableA.Contents) Like "**lg-vx3200-car**" Or (TableA.Contents) Like "**lg-VX3300**" Or (TableA.Contents) Like "**lg-VX3400**" Or (TableA.Contents) Like "**lg-VX3450**" Or (TableA.Contents) Like "**LG-vx4500**" Or (TableA.Contents) Like "**LG-vx4600**" Or (TableA.Contents) Like "**lg-VX4650**" Or (TableA.Contents) Like "**lg-VX4700**" Or (TableA.Contents) Like "**lg-VX5200**" Or (TableA.Contents) Like "**LG-VX6000**" Or (TableA.Contents) Like "**LG-VX6000-car**" Or (TableA.Contents) Like "**LG-VX6000-chargers**" Or (TableA.Contents) Like "**lg-vx6000-home**" Or (TableA.Contents) Like "**lg-VX6100**" Or (TableA.Contents) Like "**LUCGOLLGLGGE**" Or (TableA.Contents) Like "**LX1200-BATTERIES**" Or (TableA.Contents) Like "**LX1200-CAR**" Or (TableA.Contents) Like "**LX1200-CASE**" Or (TableA.Contents) Like "**LX1200-CHARGER**" Or (TableA.Contents) Like "**LX1200-HOME**" Or (TableA.Contents) Like "**LX5350-BLUE**" Or (TableA.Contents) Like "**LX5350-SILVER**" Or (TableA.Contents) Like "**NONAME1**" Or (TableA.Contents) Like "**OEMAUDLITION110**" Or (TableA.Contents) Like "**OEMAUDLITION19**" Or (TableA.Contents) Like "**OEMAUDLITION21**" Or (TableA.Contents) Like "**original-VX3200-battery**" Or (TableA.Contents) Like "**original-VX3300**" Or (TableA.Contents) Like "**original-VX3400**" Or (TableA.Contents) Like "**original-VX3400-extended-battery**" Or (TableA.Contents) Like "**original-VX3450**" Or (TableA.Contents) Like "**original-VX3450-extended-battery**" Or (TableA.Contents) Like "**original-VX3450-holster**" Or (TableA.Contents) Like "**original-VX4650**" Or (TableA.Contents) Like "**original-VX4650-extended-battery**" Or (TableA.Contents) Like "**original-VX4700**" Or (TableA.Contents) Like "**original-vx4700-extended-battery**" Or (TableA.Contents) Like "**original-VX5200**" Or (TableA.Contents) Like "**original-vx6100**" Or (TableA.Contents) Like "**PHONALGLGICL**" Or (TableA.Contents) Like "**RAPBATCHARLG1**" Or (TableA.Contents) Like "**RAPCARCHARLG**" Or (TableA.Contents) Like "**RAPCARCHARLG2**" Or (TableA.Contents) Like "**RAPCARCHARLG3**" Or (TableA.Contents) Like "**SONYCLA**" Or (TableA.Contents) Like "**sony-m2300-battery**" Or (TableA.Contents) Like "**VX10-ANTENNA**" Or (TableA.Contents) Like "**VX10-BLUE**" Or (TableA.Contents) Like "**VX10-CASE**" Or (TableA.Contents) Like "**VX10-CHARGER**" Or (TableA.Contents) Like "**VX10-SILVER**" Or (TableA.Contents) Like "**VX1-ANTENNA**" Or (TableA.Contents) Like "**VX1-BLUE**" Or (TableA.Contents) Like "**VX1-CAR**" Or (TableA.Contents) Like "**vx1-case**" Or (TableA.Contents) Like "**VX1-ORIGINAL**" Or (TableA.Contents) Like "**vx3200-BATTERIES**" Or (TableA.Contents) Like "**vx3200-CHARGER**" Or (TableA.Contents) Like "**vx3200-HEADSET**" Or (TableA.Contents) Like "**vx3200-HOME**" Or (TableA.Contents) Like "**VX3300-BATTERIES**" Or (TableA.Contents) Like "**VX3300-battery**" Or (TableA.Contents) Like "**VX3300-CAR**" Or (TableA.Contents) Like "**VX3300-CASE**" Or (TableA.Contents) Like "**VX3300-CHARGER**" Or (TableA.Contents) Like "**VX3300-HEADSET**" Or (TableA.Contents) Like "**VX3300-HOME**" Or (TableA.Contents) Like "**VX3400-BATTERIES**" Or (TableA.Contents) Like "**VX3400-battery**" Or (TableA.Contents) Like "**VX3400-CAR**" Or (TableA.Contents) Like "**VX3400-cases**" Or (TableA.Contents) Like "**VX3400-CHARGER**" Or (TableA.Contents) Like "**VX3400-HEADSET**" Or (TableA.Contents) Like "**VX3400-HOME**" Or (TableA.Contents) Like "**VX3400-pouch**" Or (TableA.Contents) Like "**VX3450-BATTERIES**" Or (TableA.Contents) Like "**VX3450-battery**" Or (TableA.Contents) Like "**VX3450-CAR**" Or (TableA.Contents) Like "**VX3450-CASE**" Or (TableA.Contents) Like "**VX3450-cases**" Or (TableA.Contents) Like "**VX3450-CHARGER**" Or (TableA.Contents) Like "**VX3450-HEADSET**" Or (TableA.Contents) Like "**VX3450-HOLDER**" Or (TableA.Contents) Like "**VX3450-HOLSTER**" Or (TableA.Contents) Like "**VX3450-HOME**" Or (TableA.Contents) Like "**VX3450-pouch**" Or (TableA.Contents) Like "**vx4500-BATTERIES**" Or (TableA.Contents) Like "**vx4500-BATTERY**" Or (TableA.Contents) Like "**vx4500-CAR**" Or (TableA.Contents) Like "**vx4500-CHARGER**" Or (TableA.Contents) Like "**vx4500-HEADSET**" Or (TableA.Contents) Like "**vx4500-HOME**" Or (TableA.Contents) Like "**VX4500-pouch**" Or (TableA.Contents) Like "**vx4500-premium-car**" Or (TableA.Contents) Like "**vx4500-silver-car**" Or (TableA.Contents) Like "**vx4600-BATTERIES**" Or (TableA.Contents) Like "**vx4600-BATTERY**" Or (TableA.Contents) Like "**vx4600-CAR**" Or (TableA.Contents) Like "**vx4600-CASE**" Or (TableA.Contents) Like "**vx4600-CHARGER**" Or (TableA.Contents) Like "**vx4600-HEADSET**" Or (TableA.Contents) Like "**vx4600-HOME**" Or (TableA.Contents) Like "**vx4600-premium-car**" Or (TableA.Contents) Like "**vx4600-silver-car**" Or (TableA.Contents) Like "**VX4650-BATTERIES**" Or (TableA.Contents) Like "**VX4650-battery**" Or (TableA.Contents) Like "**VX4650-CAR**" Or (TableA.Contents) Like "**VX4650-CHARGER**" Or (TableA.Contents) Like "**VX4650-HEADSET**" Or (TableA.Contents) Like "**VX4650-HOME**" Or (TableA.Contents) Like "**VX4650-pouch**" Or (TableA.Contents) Like "**VX4700-BATTERIES**" Or (TableA.Contents) Like "**VX4700-battery**" Or (TableA.Contents) Like "**VX4700-CAR**" Or (TableA.Contents) Like "**VX4700-CHARGER**" Or (TableA.Contents) Like "**vx4700-door**" Or (TableA.Contents) Like "**VX4700-HEADSET**" Or (TableA.Contents) Like "**VX4700-HOME**" Or (TableA.Contents) Like "**vx4700-pouch**" Or (TableA.Contents) Like "**VX5200-BATTERIES**" Or (TableA.Contents) Like "**VX5200-battery**" Or (TableA.Contents) Like "**VX5200-CAR**" Or (TableA.Contents) Like "**VX5200-CHARGER**" Or (TableA.Contents) Like "**VX5200-HEADSET**" Or (TableA.Contents) Like "**VX5200-HOME**" Or (TableA.Contents) Like "**VX5200-pouch**" Or (TableA.Contents) Like "**VX6000-BATTERIES**" Or (TableA.Contents) Like "**vx6000-battery**" Or (TableA.Contents) Like "**vx6000-car**" Or (TableA.Contents) Like "**VX6000-CASE**" Or (TableA.Contents) Like "**VX6000-HEADSET**" Or (TableA.Contents) Like "**VX6000-HOLDER**" Or (TableA.Contents) Like "**VX6000-HOLSTER**" Or (TableA.Contents) Like "**vx6000-premium-car**" Or (TableA.Contents) Like "**VX6100-BATTERIES**" Or (TableA.Contents) Like "**VX6100-battery**" Or (TableA.Contents) Like "**VX6100-CAR**" Or (TableA.Contents) Like "**VX6100-CHARGER**" Or (TableA.Contents) Like "**VX6100-HEADSET**" Or (TableA.Contents) Like "**VX6100-HOLDER**" Or (TableA.Contents) Like "**VX6100-HOLSTER**" Or (TableA.Contents) Like "**VX6100-HOME**" Or (TableA.Contents) Like "**8700g-car-adapter**" Or (TableA.Contents) Like "**8703e-premium-car**" Or (TableA.Contents) Like "**8703e-car-adapter**" Or (TableA.Contents) Like "**7130e-car-adapter**" Or (TableA.Contents) Like "**7100i-car-adapter**" Or (TableA.Contents) Like "**8703e-BATTERIES**" Or (TableA.Contents) Like "**7130e-BATTERIES**" Or (TableA.Contents) Like "**8703e-chargers**" Or (TableA.Contents) Like "**7130e-premium-car**" Or (TableA.Contents) Like "**7100i-premium-car**" Or (TableA.Contents) Like "**8700g-premium-car**" Or (TableA.Contents) Like "**8700g-BATTERIES**" Or (TableA.Contents) Like "**7100i-BATTERIES**" Or (TableA.Contents) Like "**8703e-extended**" Or (TableA.Contents) Like "**8703e-std-battery**" Or (TableA.Contents) Like "**7100i-std-battery**" Or (TableA.Contents) Like "**7130e-std-battery**" Or (TableA.Contents) Like "**8700g-std-battery**" Or (TableA.Contents) Like "**8700g-milante-case**" Or (TableA.Contents) Like "**8703e-milante-case**" Or (TableA.Contents) Like "**7100i-horizontal-case**" Or (TableA.Contents) Like "**7130e-horizontal-case**" Or (TableA.Contents) Like "**8700g-original-case**" Or (TableA.Contents) Like "**8703e-original-case**" Or (TableA.Contents) Like "**7100i-original-HOLSTER**" Or (TableA.Contents) Like "**8703e-case**" Or (TableA.Contents) Like "**8700g-case**" Or (TableA.Contents) Like "**7130e-cases**" Or (TableA.Contents) Like "**7100i-cases**" Or (TableA.Contents) Like "**7100i-chargers**" Or (TableA.Contents) Like "**7130e-chargers**" Or (TableA.Contents) Like "**8700g-chargers**" Or (TableA.Contents) Like "**original-blackberry-7100i-earbud**" Or (TableA.Contents) Like "**original-blackberry-7130e-earbud**" Or (TableA.Contents) Like "**original-blackberry-8700g-earbud**" Or (TableA.Contents) Like "**original-blackberry-8703e-earbud**" Or (TableA.Contents) Like "**8700g-HOLSTER**" Or (TableA.Contents) Like "**8703e-HOLSTER**" Or (TableA.Contents) Like "**7100i-HOLDER**" Or (TableA.Contents) Like "**7130e-HOLDER**" Or (TableA.Contents) Like "**8700g-HOLDER**" Or (TableA.Contents) Like "**8703e-HOLDER**" Or (TableA.Contents) Like "**7130e-original-HOLSTER**" Or (TableA.Contents) Like "**8703e-headset**" Or (TableA.Contents) Like "**8700g-headset**" Or (TableA.Contents) Like "**7100i-headset**" Or (TableA.Contents) Like "**7130e-headset**" Or (TableA.Contents) Like "**Blackberry-8703e**" Or (TableA.Contents) Like "**original-8700g-white-wall**" Or (TableA.Contents) Like "**original-7130e-black-wall**" Or (TableA.Contents) Like "**Blackberry-7100i**" Or (TableA.Contents) Like "**Blackberry-7130e**" Or (TableA.Contents) Like "**Blackberry-8700g**" Or (TableA.Contents) Like "**original-7100i-white-wall**" Or (TableA.Contents) Like "**original-7130e-white-wall**" Or (TableA.Contents) Like "**original-7100i-black-wall**" Or (TableA.Contents) Like "**original-8700g-black-wall**" Or (TableA.Contents) Like "**original-8703e-black-wall**" Or (TableA.Contents) Like "**original-8703e-white-wall**" Or (TableA.Contents) Like "**original-7100i**" Or (TableA.Contents) Like "**original-7130e**" Or (TableA.Contents) Like "**original-8700g**"))
ORDER BY TableA.Contents;

 

[PHV]

Are you sure you need the Like operator instead of = ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CJSwed]

Thanks for the suggestion. I gave it a shot replacing the like with a = but it still comes back with a error message of the query being to complex.

 

[MajP]

Wouldn't be a lot easier to build a table with all of those values. Then a delete query something like

where tableA.contents in (select delContents from tblDeleteContents)

 

[MajP]

you could even just do an inner join to the table with values.

 

[PHV]

If the = operator is fine then you may use the IN operator:
WHERE TableA.Contents IN ('some value','another value',...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CJSwed]

Thanks guys.. I ended up just breaking down the list to smaller ones and it worked. I guess it was just to many keywords (354) which produced the error. This is something I encounter frequently so I will print out this thread and mess around with the different ways. I especially like where tableA.contents in (select delContents from tblDeleteContents) since it seems the quickest and possibly the easiest way. I'll have to mess around with a sample database ...

Thanks guys!

 

[CJSwed]

Guys,
I was just looking over how to use the delete and I couldnt figure it out.

Lets say all the IDs that need to be deleted are in lets say Table1 , column A and I wish to delete every instance of this ID from TableA, column "contents". What would the SQL look like? Keep in mind more than one ID can be in each field of "contents".

Thanks for any input...

Chris

 

[CJSwed]

Or to better explain...
Table1, Column A contains a list of words that need to be deleted from the contents column in TableData ...

I attached a sample copy of the Database...

 

[MajP]

DELETE
TableData.*
FROM
TableData
WHERE
TableData.Item
In
(SELECT TableData.Item FROM Table1 WHERE TableData.Contents Like '*' & [table1].[A] & '*')

 

[CJSwed]

I totally appreciate you doing the SQL for that! I noticed that it deletes the entire record though.. is there a way to just have it delete the IDs (which are listed in table1, column A) from the TableData, Contents column. Sort of like a search and delete? I ask this question in general and not to just MajP.

 

[MajP]

Your terminology is a little off. You are not deleting the ID you are just updating the ID to null. Your demo did not have an ID field. Also the ID field can not be an autonumber, nor a required field.

UPDATE
TableData
SET
ID = Null
WHERE
TableData.Item
In
(SELECT TableData.Item FROM Table1 WHERE TableData.Contents Like '*' & [table1].[A] & '*');

 

[CJSwed]

MajP.. Thanks... if I use:

UPDATE TableData SET TableData.Contents = Null
WHERE (((TableData.Item) In (SELECT TableData.Item FROM Table1 WHERE TableData.Contents Like '*' & [table1].[A] & '*')));


It is still wiping out the entire contents column.

What I am trying to do is say lets say I made Table1 and put a column in that table and called the column, Column A. In ColumnA I have the words that need to be deleted from the contents column of TableData.

Column A
Hello
Sue
Mary

Then there is the main database, TableData and looks like this. What I am trying to do is delete every instance of the list in the table above in the contents column of the table below:

Item	            Contents
Example	              Hello there
Another Example	      Sue was tired
A Third Example	      Mary said hello while running
A Fourth Example         Rob said Hello

The end result would look something like:

Item	         Contents
Example	              there
Another Example	      was tired
A Third Example	      said while running
A Fourth Example         Rob said

The current sql end up wiping out all the words in the contents column but what I am trying to do is just delete the individual words

 

[MajP]

I totally misunderstood what you were asking. I could not get it to work in SQL (should be able to), but here is a simple procedure that does it in code

Public Sub delKeyWords(tblContents As String, fldContents As String, tblKeyWord As String, fldKeyWord As String)
  Dim rsKeyWord As DAO.Recordset
  Dim rsContent As DAO.Recordset
  Dim strSqlKeyWord As String
  Dim strSqlContent As String
  Dim strContents As String
  Dim strKeyWord As String
  
  strSqlKeyWord = "Select " & fldKeyWord & " From " & tblKeyWord
  Set rsKeyWord = CurrentDb.OpenRecordset(strSqlKeyWord)
  
  Do While Not rsKeyWord.EOF
    strKeyWord = rsKeyWord.Fields(fldKeyWord)
    strSqlContent = "Select " & fldContents & " FROM " & tblContents & " WHERE " & fldContents & " LIKE '*" & strKeyWord & "*'"
    Set rsContent = CurrentDb.OpenRecordset(strSqlContent, dbOpenDynaset)
    Debug.Print strSqlContent
    Do While Not rsContent.EOF
      strContents = rsContent.Fields(fldContents)
      strContents = Replace(strContents, strKeyWord, "")
      Debug.Print strContents
      rsContent.Edit
        rsContent.Fields(fldContents) = strContents
      rsContent.Update
      rsContent.MoveNext
    Loop
    rsKeyWord.MoveNext
  Loop
End Sub

to call this procedure

delkeywords "tabledata","contents","table1","A"

It reads through the key word table. For each keyword it returns all records containing the key word. It removes the key word from the string.

This should work for any two tables just pass the two table names and the appropriate field names.

 

[CJSwed]

MajP, Thank you!! You have been way to kind and way to cool... thank you, I totally appreciate it.

This is going to sound kind of funny, for the last oh I dont know maybe 7, 8 years I have been using Access to store information and I use SQL somewhat frequently but never VBA.

I assume the code above is VBA? If so, I will look up how to implement it but just want to make sure I am looking up the right thing.

 

[MajP]

Sounds good. But here is the SQL:

UPDATE 
 Table1, 
 TableData 
SET 
 TableData.Contents = Replace([contents],[table1].[A],"")
WHERE 
 TableData.Contents Like '*' & [table1].[A] & '*'

 

[CJSwed]

Maj, Wow!! You are awesome! Thank you.. that worked perfectly and will come in very useful!!!