Replacing a character in a string

I recently updated some coding I did on postcode fixes to incorporate some checks for common mistakes. Some people mistakenly put an O instead of a 0, or an S instead of a 5.  I needed a way to check for this, and do the replacement.

Strings in Access are immutable – meaning they can’t be edited once created. They can be overwritten, and a common method for doing this is by using the Replace function.

Replace ( string1, find, replacement, [start, [count, [compare]]] )

However, this will replace any occurrence of it that it can find. So, if you have a postcode of S23 SGH, and you want to replace the second S with a 5, the replace function will replace both of them and create 523 5GH.

To help, I wrote a small function that will replace a character in a given location – meaning you can specify which one you want to replace. This is particularly useful in postcode checks. Here is the function:

Function ReplaceChar(Original As String, LocationFromLeft As Integer, Replacement As String) As String

Dim Updated As String
 Dim Length As Integer
 
 Length = Len(Original)
 Updated = Left(Original, LocationFromLeft - 1) & Replacement & Right(Original, Length - LocationFromLeft)
 ReplaceChar = Updated

End Function

This worked just as I wanted it to, and allowed me to replace the third character from the end with the correct number – changing S23 5GH.