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
Solved

VBA Replace Not Working

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

790 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