UK postcodes in MS Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zippy - 15 Dec 2005 12:35 GMT

I want to make all UK PostCodes in a MS Access (XP) table show exactly 8
digits. Some are only 7. For example TR14 8SP is 8 digits, whereas TR1 8SP
is 7 digits long. I need a formula that will look at the postcode and
convert 7 digit postcodes to 8, i.e. TR1 8SP becomes TR01 8SP.

My aim is to have 4 fields in the table: 1. The postcode as it comes to me,
2. The Left-hand half of the PostCode (TR1), 3. The Right-hand half of the
PostCode (ensuring that there is a space between the two halves) and finally
4. The combined L + R halves with a zero in front of one-digit numbers where
needed as described above. All fields to be in the same table ideally.

A tall order I fear, but one that will entertain someone over a lunchtime
perhaps?

Reply to this Message

Keith W - 15 Dec 2005 13:00 GMT

>I want to make all UK PostCodes in a MS Access (XP) table show exactly 8
> digits. Some are only 7. For example TR14 8SP is 8 digits, whereas TR1
[quoted text clipped - 13 lines]
> A tall order I fear, but one that will entertain someone over a lunchtime
> perhaps?

Well some may consider this to be overkill but maybe the most straighforward
way would be to store "TR" in a separate field to "1" and format the "1"
field such that it displays the leading zero for free. You *could* write
some code to do it but, if that's all you want, why bother?

Just my 2p worth.

Keith.
www.keithwilby.com

Reply to this Message

John Nurick - 15 Dec 2005 18:08 GMT

So what do you do with 6-character postcodes such as B1 3AA?

>I want to make all UK PostCodes in a MS Access (XP) table show exactly 8
>digits. Some are only 7. For example TR14 8SP is 8 digits, whereas TR1 8SP
[quoted text clipped - 9 lines]
>A tall order I fear, but one that will entertain someone over a lunchtime
>perhaps?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message