replace vba

Posted on 2014-09-18
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 250 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 250 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"

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

691 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