Solved

VBA Replace Not Working

Posted on 2013-11-21
3
540 Views
Last Modified: 2013-11-21
I'm having an issue with using the Replace function in a MS Access function.  I'm looping through a table and replacing hyperlinks that are within text with Space(0) but for some reason the links aren't being found and replaced.

 Set rs1 = db.OpenRecordset("SELECT DocLink FROM Documents")
 Set rs2 = db.OpenRecordset("SELECT DocDesc FROM Documents")
 
 Dim FinalClean As String
 
  rs1.MoveFirst
  rs2.MoveFirst
  
    Do Until rs1.EOF
        Do Until rs2.EOF
         rs2.Edit
         
         FinalClean = rs2![DocDesc]
         FinalClean = Replace(FinalClean, rs1![DocLink], Space(0), , vbTextCompare)
         FinalClean = Replace(FinalClean, Chr(34), Space(0))
         FinalClean = Replace(FinalClean, ")", Space(1))
         FinalClean = Replace(FinalClean, "(", Space(1))
         FinalClean = Replace(FinalClean, Space(2), Space(1))
                    
         rs2![DocDesc] = FinalClean
         rs2.Update
         rs2.MoveNext
        Loop
      rs1.MoveNext
     Loop

Open in new window


Anybody have an idea about why this isn't working?
0
Comment
Question by:redeux-tech
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 250 total points
ID: 39667330
with the version of the function with the optional compare parameter you need to advance by two commas, like this:
FinalClean = Replace(FinalClean, rs1![DocLink], Space(0), , , vbTextCompare)

Open in new window

A few notes:
1. I think that instead of using Space(0) you should use "", instead of Space(1) use " ". There is no point to use this function. Excessive use of it may slow down the program.
2. I believe vbTextCompare is a default parameter. You can just omit it altogether.
3. I think you need to switch the loops. You are making rs2 Updates for all rows times the number of rows in rs1. If you switch the loops you will make only so many updates as many rows in rs2
4. You do not need to replace ")", "(", etc. for every single row in rs1. It is enough to make it once
5. And lastly, you need to reset the inner loop's dataset to the first row, using MoveFirst

So, based on the notes above the whole function can be rewritten like this:
Set rs1 = db.OpenRecordset("SELECT DocLink FROM Documents")
 Set rs2 = db.OpenRecordset("SELECT DocDesc FROM Documents")
 
 Dim FinalClean As String
 
  rs1.MoveFirst
  rs2.MoveFirst
  
    Do Until rs2.EOF
        rs2.Edit
         
        FinalClean = rs2![DocDesc]

        ' reset to the first row
        rs1.MoveFirst
        Do Until rs1.EOF
         FinalClean = Replace(FinalClean, rs1![DocLink], "")
         rs1.MoveNext
        Loop

        ' Do these replaces once only. Do not place them inside the rs1 loop
        FinalClean = Replace(FinalClean, Chr(34), "")
        FinalClean = Replace(FinalClean, ")", " ")
        FinalClean = Replace(FinalClean, "(", " ")
        FinalClean = Replace(FinalClean, "  ", " ")
                    
        rs2![DocDesc] = FinalClean
        rs2.Update
        rs2.MoveNext
     Loop

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 39667384
The replace question was answered in my first sentence. The additional notes were just the side notes. I found some problems in your code I thought would be wise to point. You are free to implement just the suggestions regarding the replace function, like this (please note two commas):
FinalClean = Replace(FinalClean, rs1![DocLink], Space(0), , , vbTextCompare)

Open in new window

0
 
LVL 1

Author Closing Comment

by:redeux-tech
ID: 39667385
Exactly what I needed.  Thanks for the fresh eyes!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

943 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

4 Experts available now in Live!

Get 1:1 Help Now