MS Access: Replace apostrophe with empty string

Question

I'm trying to remove apostrophes from names throughout a column. The code below runs without error but results in no changes.

Call ReplaceChar(tbl, col, "'", "")

Public Sub ReplaceChar(tbl, col, oldChar, newChar)

'Removes all characters that occur in value

 

    Dim result As String

    Dim param As String

 

    result = Replace("[" & tbl & "].[" & col & "]", oldChar, newChar)

    param = InStr("[" & tbl & "].[" & col & "]", oldChar)

 

    DoCmd.RunSQL "update [" & tbl & "]" & _

        "set [" & tbl & "].[" & col & "] = " & result & _

        "where " & param & " <> 0"

 

End Sub

I added the where statement based on another post I read to limit action to only values with string to be replaced. I also attempted to use a like statement with *'* instead of instr but that didn't work either. I tested the where instr statement and found that it always returns 0, even though the input value certainly has an apostrophe.

Answer 1

result = Replace("[" & tbl & "].[" & col & "]", oldChar, newChar)

This line replaces quote mark in the name of the table and column.

If we pass as parameters tbl = table1, col = column1 result will be [table1].[column1], while you expected Replace([table1].[column1], oldChar, newChar)

Same with the next line.

Use

result = "Replace([" & tbl & "].[" & col & "], """ & oldChar & """, """ & newChar & """)"
param = "InStr([" & tbl & "].[" & col & "], """ & oldChar & """)"

instead