Solved

replace vba

Posted on 2014-09-18
10
206 Views
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
0
Comment
Question by:PeterBaileyUk
  • 4
  • 4
  • 2
10 Comments
 
LVL 57

Accepted Solution

by:
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.

Jim.
0
 
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 & " ",""))
0
 
LVL 57
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 ")

or

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

Jim.
0
 

Author Comment

by:PeterBaileyUk
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
0
 
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 & " ",""))
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:PeterBaileyUk
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.
0
 
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,

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

results in

Ki
0
 

Author Comment

by:PeterBaileyUk
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
0
 
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 & " ",""))
0
 

Author Comment

by:PeterBaileyUk
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)

 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now