Find and Replace

[alexfwalker81]

I'm guessing that @alexfwalker81 is trying to remove completely some string in the field (ie replace with nothing), but the field in the table (new target) is Null rather than a zls.

Again guessing, but the answer is probably:

' ...
      strinput = Replace(strinput, .Fields("Target"), Nz(.Fields("new target"), vbNullString))
' ...

If Target is Null then you're really in trouble!

Lightbulb moment.

In my 'Subs' table, the final row was Null and Null. Having quickly imported it from Excel, I didn't notice the blank row in Excel, or once uploaded. I've just deleted that row and it seems to work perfectly.

GPGeorge, I take your point in relation to simplification. I had to simplify my example table simply because the contents are commercially sensitive. Your point is correct in relation to strings being substrings of others, especially where I'm working with URLs. Now that I've got the framework set up correctly though, I can address that.

Thank you for your help and patience.

 

[Pat Hartman]

Find and Replace is extremely dangerous. Always make backups before running the procedure and check results carefully. If your string is not completely unique, you could end up changing code/records that you did not intend to change.

 

[GPGeorge]

Lightbulb moment.

In my 'Subs' table, the final row was Null and Null. Having quickly imported it from Excel, I didn't notice the blank row in Excel, or once uploaded. I've just deleted that row and it seems to work perfectly.

GPGeorge, I take your point in relation to simplification. I had to simplify my example table simply because the contents are commercially sensitive. Your point is correct in relation to strings being substrings of others, especially where I'm working with URLs. Now that I've got the framework set up correctly though, I can address that.

Thank you for your help and patience.

a note to that effect goes a long way to reducing ambiguity.

 

[cheekybuddha]

Since this will be a one-off/rare operation it would be more efficient to use @theDBguy 's suggested method.

You can use a single function to do the whole operation without using my suggested function in a separate UPDATE query.

Along the lines of:

Function FixURLs() As Boolean

  Const FIND_REPLACE_VALUES As String = "SELECT Target, [New target] FROM Subs;"
  Dim db As DAO.Database, strSQL As String
 
  Set db = CurrentDb
  With db.OpenRecordset(FIND_REPLACE_VALUES)
    Do While Not .EOF
      If Len(.Fields("Target") & vbNullString) _
      And Len(.Fields("New Target") & vbNullString) Then
      ' Substitute table and field names as required
        strSQL = "UPDATE FirstTable " & _
                 "SET URLField = " & _
                   "Replace(URLField, '" & .Fields("Target") & "', '" & .Fields("New Target") & "');"
        db.Execute strSQL, dbFailOnError
      End If
      .MoveNext
    Loop
    .Close
  End With
  Set db = Nothing
  FixURLs = (Err = 0)

End Function

As suggested by both myself and Pat, make a backup of your table first.
Then you can just run from the Immediate Window (Ctrl+G):

?FixURLs