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]

Open in new window


Open in new window

PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
Maybe I'm missing something - have you thought about using the vba Replace function?

replace(variable,"lookForThis","ReplaceWithThis")
0
PeterBaileyUkAuthor Commented:
yes I did that first, but i want to eliminate the whole word so if i say find 325 but the word in the string to search has 325st how do i replace the 325st as a whole and not just the substring.
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
If it's a "word" then it's surrounded by spaces.

replace " 325 " with " something "

MAYBE you next a couple replaces to cover " 325, " and " 325. "
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PeterBaileyUkAuthor Commented:
if i did replace("my string 325st","325","") i would get

"my string st"

what I would like is:
"my string"
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
I see your issue now.

I would suggest looking over the solution here:
http://www.experts-exchange.com/Database/MS_Access/Q_28173264.html

It appears you will have to use a RegEx expression in your replacing.
0
MacroShadowCommented:
Try this function:
Function CustomRemove(strInput As String, strSearch As String) As String

    Dim strArray() As String
    Dim i As Long
    Dim strTemp As String

    strArray = Split(strInput, " ")

    For i = LBound(strArray) To UBound(strArray)
        If InStr(1, strArray(i), strSearch) > 0 Then
            strArray(i) = ""
        End If
        strTemp = strTemp & strArray(i) & " "
    Next
    
    CustomRemove = Replace(strTemp, "  ", " ")
    
End Function

Open in new window


Sample usage:
msgbox CustomRemove("3 SERIES SALOON 323i 4dr","323")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
went with this function as it was small and efficient and I can call it when needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.