| | Feedback | Various examples of access queries | | ||
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.
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 |
