Solved

VBA Replace Not Working

Posted on 2013-11-21
3
518 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
Comment Utility
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
Comment Utility
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
Comment Utility
Exactly what I needed.  Thanks for the fresh eyes!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL 2010 7 39
Best way to export and then import DHCP to a new server? 6 26
Oart.dll 2 24
MS Word Formatting 3 25
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

771 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

12 Experts available now in Live!

Get 1:1 Help Now