replace vba

Posted on 2014-09-18
Medium Priority
Last Modified: 2014-09-22
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
Question by:PeterBaileyUk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
LVL 58

Accepted Solution

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

LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 1000 total points
ID: 40330041
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 & " ",""))
LVL 58
ID: 40330044
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 ")


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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 40330047
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
LVL 24

Expert Comment

by:Phillip Burton
ID: 40330050
I suggest:

RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Trim(Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value & " ", RstAutoRange.Fields("lookfor").Value & " ",""))

Author Comment

ID: 40332567
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.
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332577
My solution above deals with the "end of the string" problem.

The Replace function is not case sensitive. For example,


results in


Author Comment

ID: 40332725
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
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332732
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 & " ",""))

Author Comment

ID: 40332998
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)

    countrecords = RstWordGroups.RecordCount

DoCmd.SetWarnings False

With RstWordGroups

    countrecords = .RecordCount

 'cycle through the recordset
    Do Until RstWordGroups.EOF

        With RstAutoRange
            Do Until RstAutoRange.EOF
            If RstAutoRange.Fields("PreserveWord").Value = False Then
'            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"))
            'preserved word
            End If
        End With
'            RstWordGroups.Edit
'            RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value = Replace(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, "Remove", " RemoVE ")
'            RstWordGroups.Update

End With

MsgBox "finished extended data pre clean"

End Sub

Open in new window


Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question