alfamikefoxtrot
asked on
MS Access - loop through contents of table and iteratively find/replace words from second table
I have two tables, Table1 and Table2. Table1 has a column called Dump which contains a bunch of string data. I want to iteratively go through the string data in Dump and remove certain reference words that exist in Table2 (these words exist in a column called Word). I'd like to do this programmatically (seems cleaner) but am open to doing it in a query. I keep getting a variety of errors depending on what I change -- ranging from "member not found" to "two few parameters." Here's the code as it exists now.
Edit: Code placed in snippet box - GrahamSkan
Dim dbs As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim iDataID As String
Dim WordData As String
Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("SELECT [Table1].[Dump] FROM Table1")
Set rs2 = dbs.OpenRecordset("SELECT [Table2],[Word] FROM Table2")
rs2.MoveFirst
Do While Not rs2.EOF
iDataID = rs2.Fields("Word").Value
WordData = rs1.Fields("Dump").Value
rs1.MoveFirst
Do While Not rs1.EOF
WordData = Replace(rs1.Fields("Dump"), iDataID, " ")
rs1.MoveNext
Loop
rs1.MoveFirst
rs2.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set dbs = Nothing
I could probably solve this over the next few knuckleheaded days but seems better to ask the experts. Feedback welcomed.Edit: Code placed in snippet box - GrahamSkan
can you provide some sample data and expected output.
ASKER
Sure --
Original contents of Table1:
ID Dump
1 this is a test. this is only a test.
2 the quick brown fox jumps over the lazy dog.
Contents of Table2:
ID Word
1 the
2 this
I want the code to be able to remove from Table1.Dump the words that appear in Table2.Word, resulting in the contents of Table1 now looking like:
ID Dump
1 is a test. is only a test.
2 quick brown fox jumps over lazy dog.
Of note -- the contents of Table 2 will vary (e.g., different words can be present in table) and there can be anywhere from a couple to 20 or 30 that would need to iteratively be removed.
Original contents of Table1:
ID Dump
1 this is a test. this is only a test.
2 the quick brown fox jumps over the lazy dog.
Contents of Table2:
ID Word
1 the
2 this
I want the code to be able to remove from Table1.Dump the words that appear in Table2.Word, resulting in the contents of Table1 now looking like:
ID Dump
1 is a test. is only a test.
2 quick brown fox jumps over lazy dog.
Of note -- the contents of Table 2 will vary (e.g., different words can be present in table) and there can be anywhere from a couple to 20 or 30 that would need to iteratively be removed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys -- appreciate the commentary. The Cartesian solution in update query form looks like it's the best after all. Nice and elegant....!