Extract PostCode

L

leebo1973

Registered User.
Local time
Today, 11:07
Joined
Jan 18, 2014
Messages
25
Hi all

I have a table with a string field included for an address.

Some rows have postcodes and some dont

How can I extract the PostCode values from the field please??

Thanks
 
Show us some sample data in the table fields.
 
hi

Examples as follows:

14 Bude Crescent Stevenage Hertfordshire SG1 2RE
134 Hanging Hill Lane, Hutton, Brentwood, CM13 2HG

Thanks
 
Here is a rough draft code.
Code: 
Public Function getMePostcode(inputAddress As String) As String
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim tmpArr() As String, pLoc As Long, tmpPCode
    inputAddress = Replace(inputAddress, ",", " ")
    
    tmpArr = Split(inputAddress, " ")
    pLoc = UBound(tmpArr)
    tmpPCode = tmpArr(pLoc - 1) & " " & tmpArr(pLoc)

    [COLOR=Green]'Optional - [B]But would advice the use of this ![/B]
    'You can get the code for [B]rgxValidate()[/B] from
    'http://access.mvps.org/access/modules/mdl0063.htm[/COLOR]
    [COLOR=Green]'If Not rgxValidate(tmpPCode) Then tmpPCode = vbNullString[/COLOR]

    getMePostcode = tmpPCode
End Function
The sample tested against.
Code: 
? getMePostcode("134 Hanging Hill Lane, Hutton, Brentwood, CM13 2HG")
CM13 2HG
? getMePostcode("14 Bude Crescent Stevenage Hertfordshire SG1 2RE")
SG1 2RE
 
Thanks

Were do I put the code so that I can access it??
 
Copy the code I have given and the code from the link in the comments (within the code) and paste it into a New module.

attachment.php


Then compile the code. Then use it in a Query or Unbound control on a Form or wherever you want to get the postcode.
 
Please tell me you did the following:

  1. You DID NOT name the Module getMePostCode or rgxValidate
  2. You Saved and Compiled the code, before you used it in a Query.
  3. Tested something like what I did in the immediate window.
 
forgive me

am fairly new too this, so havent done what you ahve suggested as dont know how to????
 
Okay, scratch everythign that you did, lets do it over. Follow the Steps.

  • Copy the following code into a New module (Menu Bar ->Inset -> Module):
Code: 
[COLOR=Green]'**************CONSTANT DECLARATIONS******************
    'Some useful regular expressions:                

    'Notes:
    'Each of these regular expressions is wrapped in a (?: )
    'grouping pattern. This means that they can be OR'd by
    'concatenating them with the pipe character "|". Thus
    ' rgxZIP_US & "|" & rgxZIP_CA
    'will match either US or Canadian postal codes.
    '
    'Official formatting of postcodes and the like may change
    'over time. Some of these expressions may need adjustment
    ' to bring them up to date.  [/COLOR]              

[COLOR=Green]    'UK Postcode[/COLOR]
    Public Const rgxZIP_UK = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
    & "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
    & "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
    & "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
    & "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
    & "\d(?:\d|[A-Z])? \d[A-Z]{2})"

[COLOR=Green]    'A simpler expression that does not check for valid postcode 
    areas:
    ' "(?:[A-Z]{1,2}\d(?:\d|[A-Z])? \d[A-Z]{2})"[/COLOR]

[COLOR=Green]    'Zip or Zip+4[/COLOR]
    Public Const rgxZIP_US = "(?:\d{5}(?:-\d{4})?)"                

[COLOR=Green]    'Canadian postal codes[/COLOR]
    Public Const rgxZip_CA = "(?:[A-Z]\d[A-Z] \d[A-Z]\d)"
    
[COLOR=Green]    'Most European postal codes:[/COLOR]
    Public Const rgxZIP_EU = "(?:NL-\d{4}(?: [A-Z][A-Z])|" _
    & "(?:IS|FO)\d{3}|" _
    & "(?:A|B|CH|CY|DK|EE|H|L|LT|LV|N)-\d{4}|" _
    & "(?:BA|DE?|E|FR?|FIN?|HR|I|SI|YU)-\d{5}|" _
    & "(?:CZ|GR|S|SK)-\d{3} \d{2}|PL-\d\d-\d{3}|" _
    & "PT-\d{4}(?:-\d{3})?" _
    & ")"                

[COLOR=Green]    'A simpler expression that doesn't check the postcode
    'format against the country code[/COLOR]
[COLOR=Green]    ' "(?:NL[- ]\d{4} [A-Z][A-Z]|" _
    ' & "(?:[A-Z]{1,2}[- ])?\d{2,3}(?:\d\d?| \d\d|\d-\d{3}))"     [/COLOR]           

[COLOR=Green]    'US States[/COLOR]
    Public Const rgxSTATES_US = "(:?A[KLRZ]|C[AOT]|D[CE]|FL|" _
    & "GA|HI|I[ADLN]|K[SY]|LA|M[ADEINOST]|N[CDEHJMVY]|" _
    & "O[HKR]|P[AR]|RI|S[CD]|T[NX]|" _
    & "UT|V[AIT]|W[AIVY])"                

[COLOR=Green]    'Australian States[/COLOR]
    Public Const rgxSTATES_AU = "(?:ACT|NSW|NT|QLD|SA|TAS|VIC|WA)"                

[COLOR=Green]    'Canadian Provinces[/COLOR]
    Public Const rgxPROVINCES_CA = "(?:AB|BC|MB|N[BLTSU]|ON|PE|QC|SK|YT)"           

[COLOR=Green]    'Canonical phone number[/COLOR]
    Public Const rgxPHONE_INTL = "(?:\+\d{1,4} ?(?:\(\d{0,5}\))?(?:\d+[-. ])*\d{2,})"
[COLOR=Green]'***********************CONSTANTS END***********************[/COLOR]


 Public Function getMePostcode(inputAddress As String) As String
[COLOR=Green]'*****************************************
'Code Courtesy of
'  Paul Eugin[/COLOR]
[COLOR=Green]' The code for [B]rgxValidate()[/B] from[/COLOR]
[COLOR=Green]'http://access.mvps.org/access/modules/mdl0063.htm[/COLOR][COLOR=Green] 
[/COLOR]
[COLOR=Green]'*****************************************[/COLOR]
    Dim tmpArr() As String, pLoc As Long, tmpPCode
    inputAddress = Replace(inputAddress, ",", " ")
    
    tmpArr = Split(inputAddress, " ")
    pLoc = UBound(tmpArr)
    tmpPCode = tmpArr(pLoc - 1) & " " & tmpArr(pLoc)

[COLOR=Black]If Not rgxValidate(tmpPCode, [/COLOR]rgxZIP_UK[COLOR=Black]) Then tmpPCode = vbNullString[/COLOR]

    getMePostcode = tmpPCode
End Function


Function rgxValidate( _
        Target As Variant, _
        Pattern As String, _
        Optional CaseSensitive As Boolean = False, _
        Optional MatchWholeString As Boolean = True, _
        Optional FailOnError As Boolean = True) _
        As Boolean                
            [COLOR=Green]'*******************************************************************
            'Returns True if Target matches the regular
            'expression Pattern.                

            'By John Nurick, October 2002 - January 2003
            '©2003 John Nurick                

            'NOTES:

            'Target will normally be a String. If Target is Null,
            'rgxValidate returns False. Otherwise if Target cannot be
            'converted to a string with CStr(), rgxValidate fails
            'with Error 13, Type Mismatch.                

            'Pattern should be a VBScript regular expression. See VBScript
            'help file and other documentation for information.                

            'CaseSensitive does the expected.

            'MatchWholeString: if False, rgxValidate returns True if any
            'substring of Target matches Pattern. If True or omitted,
            'the function only returns True if the whole of Target
            'matches Pattern.
            ' E.g. Target "12345" only matches Pattern "234" if
            ' MatchWholeString is False.               

            'FailOnError: if this is True or omitted, rgxValidate passes
            'any run time error to the calling procedure. If it is False,
            'the function returns True on a successful match and False if
            'the match fails for any reason including a run time error.                

            'rgxValidate is suitable for use in data entry forms and the
            'like. It can also be used in queries and in looping through

            'recordsets, but because it creates a RegExp object and compiles
            'the regular expression (Pattern) every time it is called,
            'it is rather inefficient for repetitive operations.                
            '*******************************************************************
            
    'Constants for messages:[/COLOR]
    Const rgxPROC_NAME = "rgxValidate"
    Const rgxERRMSG_CREATE = "Could not create VBScript.RegExp object: "
    Const rgxERRMSG_UNEXPECTED = "Unexpected error: "

    [COLOR=Green]'VBScript.Regexp error messages:[/COLOR]
    Const rgxERRMSG_5017 = "Syntax error in regular expression"
    Const rgxERRMSG_5019 = "Expected ']' in regular expression"
    Const rgxERRMSG_5020 = "Expected ')' in regular expression"                

    Dim oRE As Object                

    On Error GoTo ERRHANDLER                

    rgxValidate = False 'Set default return value

    If IsNull(Target) Then Exit Function                

    Set oRE = CreateObject("VBScript.RegExp")                

    [COLOR=Green]'If we're here, the object has been created[/COLOR]
    oRE.Global = False
    oRE.IgnoreCase = Not CaseSensitive
    oRE.Multiline = False

    If MatchWholeString Then
       [COLOR=Green] 'Add anchors at ends of Pattern
        '(doesn't matter if Pattern already has them)[/COLOR]
        oRE.Pattern = "^" & Pattern & "$"
    Else
        oRE.Pattern = Pattern
    End If

   [COLOR=Green] 'Do it![/COLOR]
    rgxValidate = oRE.Test(CStr(Target))

    [COLOR=Green]'If we're here, the match executed OK. Normal termination[/COLOR]
    Set oRE = Nothing
    Exit Function
ERRHANDLER:
    If FailOnError Then
        With Err
            Select Case .Number
                Case 5017: .Description = rgxERRMSG_5017
                Case 5019: .Description = rgxERRMSG_5019
                Case 5020: .Description = rgxERRMSG_5020
            Case Else
                If oRE Is Nothing Then
                .Description = rgxERRMSG_CREATE & Err.Description
                Else
                .Description = rgxERRMSG_UNEXPECTED & Err.Description
                End If
            End Select

            Set oRE = Nothing
            Err.Raise Err.Number, , rgxPROC_NAME & "(): " & .Description
        End With
    Else [COLOR=Green]'Fail silently[/COLOR]
        Err.Clear
        Set oRE = Nothing
    End If
End Function
  • Now, save the Module by giving it the name newModOver
  • Now Compile (MenuBar -> Debug -> Compile) the code. If you get any error, solve them, if not you should now be good to go.
  • Use them in the immediate window (CTRL + G) see if it works.
  • Then you can use them in Query like,
Code: 
SELECT someAddress, getMePostcode(someAddress) As PostCode 
FROM yourTable;
Hope this helps !
 
Last edited:
have copied everything as suggested and saved as a module

have also copied all the code for the other module

When I debug though I get

Complie Erro: Arguement not optional, and it highlights the rgxValidate text
 
OOPS ! That was my fault, sorry.. In the Code, just change the If like..
Code: 
[COLOR=Black]If Not rgxValidate(tmpPCode[COLOR=Red][B], [/B][/COLOR][/COLOR][COLOR=Red][B]rgxZIP_UK[/B][/COLOR][COLOR=Black]) Then tmpPCode = vbNullString
[/COLOR]
 
thanks

now getting the following:

Compile Error: only comments may appear after End Sub, Ed Function or Ed Property

at the following:

'UK Postcode
Public Const rgxZIP_UK = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})
 
It's the copy and paste. Correct the lines under, by moving the word areas: on to the previous commented line.

So:

Code: 
    'A simpler expression that does not check for valid postcode
areas:
    ' "(?:[A-Z]{1,2}\d(?:\d|[A-Z])? \d[A-Z]{2})"

becomes:

Code: 
    'A simpler expression that does not check for valid postcode areas:
    ' "(?:[A-Z]{1,2}\d(?:\d|[A-Z])? \d[A-Z]{2})"
 
am now getting Subscript out of range and its highlights the following:

tmpPCode = tmpArr(pLoc - 1) & " " & tmpArr(pLoc)
 
am just running a query against the data that I included in my original post

Thanks
 
leebo1973 said:
am just running a query against the data that I included in my original post
Click to expand...
For the data you provided, the error will not occur, as I mentioned. I have tested the input and the code. So please check what data is giving you the problem once again !
 
Great, thanks

I've managed to get that bit working.

However some of the results are blank. ANy idea's

95 Willian Road, Hitchin, SG4 OLS has returned a blank result
 
Because SG4 OLS is not a Valid UK Post Code. Where SG4 0LS is. ;)
 
You must log in or register to reply here.

Similar threads

N
Solved Calculation Query
Replies
2
Views
168
Number11
N
H
Extract a variable number of parts of text from a string
2
Replies
26
Views
488
AHeyne
A
nicolareina
Single row data query
Replies
13
Views
355
Gasman
Gasman
C
Compile error
Replies
9
Views
370
ChrisLeicester
C
A
Solved How can I run this update query without messing everything up?
Replies
7
Views
468
aian
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom