Post Codes (Access 97 SP1)

[si_hill]

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

[HansV]

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

[Rupert]

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.

[si_hill]

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

[WendellB]

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.

[Rupert]

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.