Validate Postcode

[rtdc]

I need to import data from a dbase file and during the import I need to check that the postcodes are in the correct format, we have postcodes like ST15OTJ when they should be ST15 0TJ also we will have ST10TJ when they should be ST1 0TJ.

Does anyone have any code or clues on how to check these codes? Checking for null values is easy enough but I am having difficulty checking the rest.

Cheers.

 

[redneckgeek]

Is the problem only that the spaces are stripped out?
Do you have a "master" table of vaild post codes?
Is there some logic as to where the space should be inserted?

 

[rtdc]

I need to identify in the data coming in that the postcode is in a valid format and that it is a postcode at all, there is no master table to reference against. The space missing is not the only problem but the one I need help with, if the post code is incomplete or not a postcode I can identify those i.e. 45645 or just SA12.

I need to write some queries or code that can check that the first two characters are letters, the next is a number, the next a number or space, the next a number and the last two letters.

I am using InStr to check for a space and that seems top work it also identifies nonsense entries, left and right isnumeric should do for the start and end letters it’s the numbers in the middle. Working out if it is a 4 and 3 character postcode and where the space should be entered.

 

[raskew]

Hi -

As I suspect you're finding, you've taken on a monster.

After doing numerous Googles on UK Postcodes, have found that, as a general rule, the first character is always alphabetical and the final three characters are always a numeric character followed by two alphabetic characters but not always true, as in the case of British Forces Post Office postcodes, which have the format "BFPO NNN" or "BFPO c/o NNN", where NNN is 1 to 4 numerical digits
.
Anyway, for the majority (from the debug (immediate) window), believe I'd:
1) Remove any spaces (chr(32)) from the input postal code.
2) Reinsert chr(32) in the appropriate location
3) Check that the first character is alpha and the first character following the space is numeric.
4) If either of the above cases are false, think you need to flag this address for individual attention. Reason: the possible variations are endless and to try to write code to address every possible variation will be a losing battle.

x = "SW1A0AA"
y = despace(x, chr(32))
x = left(y, len(y) - 3) & chr(32) & right(y, 3)
? x
SW1A 0AA
flagged = iif(isnumeric(left(x, 1)) or not Isnumeric(mid(x, len(x) - 2, 1)), True, False)
? flagged
False

-or, with a typo

x = "SW1AOAA"
y = despace(x, chr(32))
x = left(y, len(y) - 3) & chr(32) & right(y, 3)
? x
SW1A OAA
flagged = iif(isnumeric(left(x, 1)) or not Isnumeric(mid(x, len(x) - 2, 1)), True, False)
? flagged
True

Here's the despace() function. Copy/paste to a standard module.

Function despace(ByVal pstr As String, pItem As String) As String
'*******************************************
'Purpose:   Removes characters from a string
'Coded by:  raskew
'Inputs:    ? despace("xx:xx:xx:xx:xx", ":")
'Output:    "xxxxxxxxxx"
'*******************************************

Dim strHold As String

    strHold = RTrim(pstr)
    Do While InStr(strHold, pItem) > 0
      strHold = Left(strHold, InStr(strHold, pItem) - 1) & Mid(strHold, InStr(strHold, pItem) + 1)
    Loop
    despace = strHold
End Function

Hope that will provide a little 'food for thought'.

Best Wishes - Bob

 

[rtdc]

Cheers Bob that is a great help, much apprecaited.

Rob