replace vba

I am doing this:
RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, RstAutoRange.Fields("lookfor").Value & " ","")

I believe the space added to the"lookfor" field I believe will stop "c 320 " having "c 32" causing a problem.

If the lookfor field word is not in the string RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value will the replace function just not do anything?

I am in vba
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Correct on both points.  Space means it will need "c 320 " and if not found, will not do anything.

Jim.
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
That is correct - if RstAutoRange.Fields("lookfor").Value & " " is not there, it will not do anything.

However, your current code with not find the word if it is at the end of the phrase, as you are looking for that extra space.

Therefore, I suggest:

RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Trim(Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value & " ", RstAutoRange.Fields("lookfor").Value & " ",""))
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, good way to test things out like this is the immediate (debug window).  In the VBA editor, press Ctrl/G.

Here you can do things like:

? Replace("c 320abc", "c 320 ")

or

If <some condition> = True then Debug.Print "YES"

Jim.
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:
damn that could be an issue how do i cure that?

and a space to RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value & " "

i know i have c 320 and c 32 that could technically come at end of string
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I suggest:

RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Trim(Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value & " ", RstAutoRange.Fields("lookfor").Value & " ",""))
0
PeterBaileyUkAuthor Commented:
Just trying it now, i checked the data and some model data was at the end of the string as predicted, one other question is replace function case sensitive? i dont want it to be.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
My solution above deals with the "end of the string" problem.

The Replace function is not case sensitive. For example,

replace("hi","H","K")

results in

Ki
0
PeterBaileyUkAuthor Commented:
thx I am just testing to see if the replace is is working it looks like its leaving models at the end:

 " RemoVE 318TD"



this one was left
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And are you looking for 318TD?

Are you using this code:

RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Trim(Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value & " ", RstAutoRange.Fields("lookfor").Value & " ",""))
0
PeterBaileyUkAuthor Commented:
yes its here you can see the two recordsets one contains the strings of words and the other the model in lookfor field

Private Sub BtnRemoveModelData_Click()
Dim Db As DAO.Database
Set Db = CurrentDb()
Dim RstAutoRange As DAO.Recordset
Set RstAutoRange = Db.OpenRecordset("QryTblProcess")
Dim wordcount As Long
Dim RstWordGroups As DAO.Recordset

Dim ClientWords As Variant
 Dim x As Long
Dim WordToRemove As String

Dim strSQL As String
Dim Tempconcatenated As String
strSQL = "SELECT ClientWordGroups.concatenated, ClientWordGroups.ManufacturerName, ClientWordGroups.Client, ClientWordGroups.ConcatenatedUnwantedRemoval, " _
& "ClientWordGroups.MarqueAlias, ClientWordGroups.OriginalString, ClientWordGroups.Groups, ClientWordGroups.ConcatenatedUnwantedRemovalCopy" _
& " FROM ClientWordGroups" _
& " WHERE (((ClientWordGroups.Groups) Is Not Null));"
Debug.Print strSQL

Set RstWordGroups = Db.OpenRecordset(strSQL)

 RstWordGroups.MoveLast
    countrecords = RstWordGroups.RecordCount

DoCmd.SetWarnings False

With RstWordGroups

    .MoveFirst
    countrecords = .RecordCount

 'cycle through the recordset
    Do Until RstWordGroups.EOF

    
    
        With RstAutoRange
            RstAutoRange.MoveFirst
            Do Until RstAutoRange.EOF
            If RstAutoRange.Fields("PreserveWord").Value = False Then
           
           
            RstWordGroups.Edit
           
'            RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, RstAutoRange.Fields("lookfor").Value, "RemoVE")
            RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Trim(Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value & " ", RstAutoRange.Fields("lookfor").Value & " ", "RemoVE"))
                      
            RstWordGroups.Update
         
            
            
            
'
            Else
            'preserved word
            
            End If
            
            
            RstAutoRange.MoveNext
            Loop
        End With
            
    
'            RstWordGroups.Edit
'
'            RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, "Remove", " RemoVE ")
'
'            RstWordGroups.Update


    RstWordGroups.MoveNext
    Loop
    
End With

MsgBox "finished extended data pre clean"

End Sub

Open in new window

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.