UK postcodes in MS Access

[Zippy]

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?

 

[Keith W]

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?

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

 

[John Nurick]

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