Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Codes (Access 97 SP1)

    Hi Guys
    Got a problem extracting the first part of a post code so it can be matched to the post offices SSC codes.
    I've tried this:-
    Query SELECT ssccode([post_code]) AS [ssc code], Friends.Post_Code
    FROM Friends;

    Module :- Public Function ssccode(post_code As String) As String
    Dim casework As String
    Dim work As String
    casework = post_code
    Select Case casework
    Case casework Like ("DA1 " & "*")
    work = "74511"
    End Select
    ssccode = work
    End Function

    I need to update a field in the Friends table to include the SSC Code......

    Any ideas ???

    Thanks

    Si

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Post Codes (Access 97 SP1)

    For all those bloody foreigners out there: this is about post codes in the UK.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UK Post Codes

    What Hans V means is that the postcode part of the Microsoft product is for checking USA and I believe Canadian postcodes. It is not designed to, or even capable of checking a UK postcode.

    I'm not surprised really because of the UK postcode is quite difficult animal to get your head round!

    The attached database contains a module for validating the format of a UK postcode.

    The information used to validate each character position, and the general format of the postcode was obtained from various web sites. The links for the web sites are in the module. I have no idea if the data I've used his correct, or up-to-date. It's entirely up to you to check for yourself, I can accept no liability!

    Although this module works, it is not the finished article and needs a lot of tidying up. I started the project several years ago and go back to it from time to time.

    One of the functions in the module is an attempt to write a procedure that would look through any string and identify a UK postcode and return its position in that string. I think I got it working sort of, but I got disillusioned with it as I realised I'd made a big mistake in my approach, I can't remember what it was even. I fully intend to go back and finish this function (when I get the time) however if anyone can throw any light on this, (don't give away too much, what am I gona do my spare time?) I would be most grateful.

    Finally, if you come across any information about UK postcodes please post it so that I can have a good chance at getting this working properly.
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UK Post Codes

    Thanks Rupert, not quite what i'm looking for....
    The UK Post office has a system for companies to save money on bulk mailing. each UK Post code has an SSC code. eg Rochester ME1, ME2 and ME3 have an SSC code of 75111 - Chatham ME4 & ME5 75121.
    I've got a database of 17500 names and addresses that need the SSC code adding.
    I'm looking for an easier way to do this than writing one query per post code, would be about 250 queries !!
    The Left function doesn't seem to work and i can't think of a way of joining the SSC table to part of the post code field.
    Hope this makes the problem clearer....
    Si

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: UK Post Codes

    Do you have a complete table with the prefix and it's 5 digit equivalent. If you do, you should be able to create a calculated expression in a query which uses the first 4 characters of the PostCode - as I recall they are either 3 or 4 digits, but always contain a space between the prefix and the suffix. You should then be able to join that on the index of the lookup table and update a field in your table of 17K addresses with the 5 digit numeric value. Hope I've understood the problem correctly.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UK Post Codes

    Ah right, well I re-read your post and I can see I got it wrong again!

    Well in the hope that I can redeem myself I found the following file, (see attachment) on the Post Office web site. It was in a comma separated value format so I have converted it and placed it in an access database and posted it here. You should now be able to match your postcode to the SSC code provided by the Post Office.

    Incidentally whilst browsing the Post Office web site I did read that it's not necessary to include the SSC codes on your address, if that's what you intended to do.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •