Solved

VBA Replace Not Working

Posted on 2013-11-21
3
596 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 25

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 25

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

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!

Question has a verified solution.

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

Suggested Solutions

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

679 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