Link to home
Start Free TrialLog in
Avatar of redeux-tech
redeux-techFlag for United States of America

asked on

VBA Replace Not Working

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?
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of redeux-tech

ASKER

Exactly what I needed.  Thanks for the fresh eyes!