MS Access VBA Function (Part 3) Address Matching – Find X Replace Y

A useful function for replacing characters or strings in a single field. This can be used in advance of address matching to increase the chances of getting matches in fields that have been collected through a UI with little or no validation.

Function FindXReplaceY(FixTable As String, FixColumn As String, X As String, Y As String) As Variant

    Dim strSQL As String

    strSQL = "UPDATE [" & FixTable & "] SET [" & FixTable & "].[" & FixColumn & "] = REPLACE([" & FixColumn & "]," & Chr$(34) & X & Chr$(34) & "," & Chr$(34) & Y & """);"
    
    DoCmd.RunSQL strSQL
    

End Function

And this is an example script that calls the above function to replace some special characters

Public Function RunFindXReplaceY()

DoCmd.SetWarnings False

    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "'", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "@", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "~", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "#", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "!", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "£", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "$", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "^", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "&", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "*", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "(", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", ")", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "-", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "+", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "=", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "?", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "|", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "\", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "/", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "{", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "}", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "[", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "]", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "`", " ")
    Call FindXReplaceY("TableNameVariable", "FieldNameVariable", "¬", " ")

DoCmd.SetWarnings True

End Function