replace a whole word in vba

I have a table that has a column called lookfor it has string type.

I have another table with a string field made of words.

If i find tableA.lookfor in the table2 field I want to remove the whole word in table 2. for example

look for value "323"
table 2 string =3 SERIES SALOON 323i 4dr

323i would be replaced by nothing ""

not sure how to achieve that.

I have two recordsets so can get to the data:

[code]

Private Sub BtnRemoveModelData_Click()
Dim Db As DAO.Database
Set Db = CurrentDb()
Dim RstAutoRange As DAO.Recordset
Set RstAutoRange = Db.OpenRecordset("TblProcess")

Dim RstWordGroups As DAO.Recordset
Set RstWordGroups = Db.OpenRecordset("ClientWordGroups")
Dim ClientWords As Variant
 Dim x As Long
Dim WordToRemove As String



DoCmd.SetWarnings False

With RstAutoRange

    .MoveFirst
    countrecords = .RecordCount

 'cycle through the recordset
    Do Until RstAutoRange.EOF

    
    
        With RstWordGroups
            RstWordGroups.MoveFirst
            Do Until RstWordGroups.EOF
                If (InStr(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, RstAutoRange.Fields("LookFor").Value) <> 0) And (RstWordGroups.Fields("MarqueAlias").Value = RstAutoRange.Fields("Marque").Value) Then
                
                'found
                'getlength
             
                
                
                
                
                
                
                Else
                'not found
                
                
                End If
    
    
    
            RstWordGroups.MoveNext
            Loop
        End With
            




    RstAutoRange.MoveNext
    Loop
    
End With



End Sub

[/code]

Select allOpen in new window

Select allOpen in new window