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.