Search a text field based on values from another table

[oshlonger]

I'm using Access 2003 and here's my problem:

I have a text field that contains a string of characters that I need to strip out. The string of characters could be anywhere in the text and may or may not be separated by spaces.

I also have a table that contains all of the strings that I'm looking for. I'm trying to search the text field in table A for any of the strings contained in table B.

For example, this will work if I know what I'm searching for: Instr(1,tableA.colum, 'BD8695H')

The problem is that I don't know what I'm searching for. I tried a sub-query in the Instr function but that is not allowed and would not be efficient:
Instr(1,tableA.colum, (select column1 from tableB))

Can someone point me in the right direction as to how I should be handling this?

 

[PHV]

A starting point:
SELECT *
FROM tableA, tableB
WHERE InStr(tableA.colum, tableB.column1)>0

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

 

[oshlonger]

Thanks - much simpler than expected. This is working right now with some dummy tables/data. I'll try it on the real thing and get back to you. I'm not sure but these tables may be quite large so I'm not sure how this will perform.

Thanks again.

 

[oshlonger]

ok, TableA is 30,000 rows and TableB is 12,000 rows. This query is just locking up my machine.

Any other suggestions?

 

[dhookom]

How about testing with a smaller dataset? Do you realize the results of the query suggested won't allow edits?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?
SELECT *
FROM tableA INNER JOIN tableB
ON tableA.colum Like '*' & tableB.column1 & '*'

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

 

[MajP]

This concept works by code if you bog down in SQL. Turn it on an let it run. Come back in a few hours.

It needs error checking, null checks, etc.

Public Sub removeText()
  Dim rsA As DAO.Recordset
  Dim rsB As DAO.Recordset
  
  Set rsA = CurrentDb.OpenRecordset("qryA", dbOpenDynaset)
  Set rsB = CurrentDb.OpenRecordset("qryB", dbOpenDynaset)
  rsB.MoveFirst
  rsA.MoveFirst
  Do While Not rsB.EOF
    Do While Not rsA.EOF
      Debug.Print rsA!A & " " & rsB!B
      If InStr(rsA.Fields("A").Value, rsB.Fields("B").Value) > 0 Then
        rsA.Edit
        rsA.Fields("A") = Replace(rsA.Fields("A"), rsB.Fields("B"), "")
        rsA.Update
      End If
      rsA.MoveNext
    Loop
    rsA.MoveFirst
    rsB.MoveNext
  Loop
End Sub

 

[oshlonger]

Thanks everyone for your help. I think the solution PHV provided will work for me. The query takes a little time to complete but its workable.

Thank you