MeadInKent
| Feedback | Various examples of access queries |

Identify and Remove Postcodes from Addresses

Occasionally a decision is taken to review and tidy up the data in large databases. Tables containing addresses of creditors or employees can easily become untidy with postcodes having been entered in different address fields instead of in a separate field. A field containing only postcodes permits better indexing and allows searches for potentially duplicated records.

Access 2010 VBA Programming

 
UK more ...   USA more ...

This example examines the Address_3 field to check for mis-entered postcodes but it can easily be adapted to also search within the other address fields.

Addresses: Table ox
CustRef Address_1 Address_2 Address_3 PostCode
1 Mansion House Castello Drive, Sevenoaks Kent SV1 1AB
3 8 Murder Lane Maidstone Kent MA46GH
4 1234 Tall Towers Putney London WC1D 8BN
2 868 Keynes View Canterbury Kent CA23 6DF

A VBA user defined function called GetPostCode (see bottom of page) is used to find the postcodes. It does this by searching the string for a variety of patterns of letters and numbers. The specific characters themselves are not important but they all follow a common sequence of 6 or 7 letters, numbers and more letters.

The user defined function is called within a select query to find and extract the postcode from the 3rd address line. It also calculates the length of the 3rd address line after the postcode has been removed.

TidyPostCodes_1: Select Query ox
CustRef Address_1 Address_2 Address_3 PostCode Add3_PostCode calc1 LenTrimAdd3
1 Mansion House Castello Drive, Sevenoaks Kent SV1 1AB SV1 1AB 5 5
3 8 Murder Lane Maidstone Kent MA46GH MA4 6GH -1 0
4 1234 Tall Towers Putney London WC1D 8BN WC1D 8BN 7 7
2 868 Keynes View Canterbury Kent CA23 6DF X 3 3

Add3_PostCode calc1 LenTrimAdd3
GetPostCode(Address_3) Len([address_3])-Len([Add3_PostCode]) IIf([calc1]<0,0,[calc1])

The second query takes a field length from the first query and returns a new shortened 3rd address field which excludes the postcode


TidyPostCodes_2: Select Query ox
CustRef Address_1 Address_2 NewAddr3 NewPostCode
1 Mansion House Castello Drive, Sevenoaks Kent SV1 1AB
3 8 Murder Lane Maidstone Kent MA4 6GH
4 1234 Tall Towers Putney London WC1D 8BN
2 868 Keynes View Canterbury Kent CA23 6DF

NewAddr3 NewPostCode
Trim(IIf([add3_postcode]="X",[address_3], Left([address_3],[lentrimadd3]))) IIf([add3_postcode]="X",[postcode], [add3_postcode])


Function GetPostCode(Optional AddressText As Variant) As String
Dim AddrTextLength As Integer, TempText As String, TempPicture As String
Dim PostCodePics(10) As String, PictureItemNum As Integer
Dim n As Integer, x As Integer

GetPostCode = "X" ' default response if no postcode detected
If IsNull(AddressText) Then
Exit Function
End If

PostCodePics(1) = "XXNSNXX" ' alternative formats of postcodes
PostCodePics(2) = "XXNNSNXX" ' where X = alpha, S = space
PostCodePics(3) = "XNNSNXX" ' and N = numeric
PostCodePics(4) = "XNSNXX"
PostCodePics(5) = "XXNNXX"
PostCodePics(6) = "XXNNNXX"
PostCodePics(7) = "XNNNXX"
PostCodePics(8) = "XNNXX"
PostCodePics(9) = "XXNXNXX"
PostCodePics(10) = "XXNXSNXX"

AddrTextLength = Len(AddressText)

If AddrTextLength < 5 Then
Exit Function
End If
If AddrTextLength <= 9 Then
TempText = Trim(AddressText)
Else
TempText = Trim(Right(AddressText, 9))
End If

PictureItemNum = 0
TempPicture = "" ' build a picture of the format of current text
For n = 1 To Len(TempText) ' detect the type of each character
x = InStr(1, "1234567890 ", Mid(TempText, n, 1))
If x > 0 And x < 11 Then TempPicture = TempPicture & "N"
If x = 11 Then TempPicture = TempPicture & "S"
If x = 0 Then TempPicture = TempPicture & "X"
Next

For n = 1 To 10 ' compare the format of the current text
x = Len(PostCodePics(n)) ' against each of the post code pictures
If Len(TempPicture) >= x Then
If Right(TempPicture, x) = PostCodePics(n) Then
PictureItemNum = n
GetPostCode = UCase(Right(TempText, x))
Exit For
End If
End If
Next

If PictureItemNum > 4 And PictureItemNum < 10 Then ' insert space in the middle if not present
GetPostCode = Left(GetPostCode, Len(GetPostCode) - 3) & " " & Right(GetPostCode, 3)
End If

End Function

A study by accountants Coopers and Lybrand has shown that 90% of spreadsheets with more than 150 rows contain errors. Check out my information on creating well designed spreadsheets.


file: acc_postcodes.htm Page last modified Apr14 MeadInKent.co.uk 2014