Solved

VBA Replace Not Working

Posted on 2013-11-21
3
611 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

739 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