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

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in Address Matching, MS Access, VBA Code MS Access. Bookmark the permalink.