Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
  • 4
  • 2
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Correct on both points.  Space means it will need "c 320 " and if not found, will not do anything.

Jim.
0
 
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)PresidentCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now