Query to place space in UK Post Code

[KenshiroUK]

Is it possible to place in a post code if a space does not exist. I played about before and got my code to place a space between characters like this

Left([ship-postal-code],Len([ship-postal-code])-3) & " " & Right([ship-postal-code],3)

However this creates a problem, if a post code has a space already in it, it will now have 2 spaces. Is there anyway to tell it to ignore the post code with space and add a space to any codes without?

 

[jdraw]

To check for an existing space, and ignore adding a new space

Iif (Instr(ship-postal-code," ")= 0,Left([ship-postal-code],Len([ship-postal-code])-3) & " " & Right([ship-postal-code],3), ship-postal-code)

However, you should not use a dash, space or special characters in field or object names. Only use alphanumerics and _. You will save yourself a lot of frustration an syntax errors.

so

Iif (Instr(shipPostalCode," ")= 0,Left([shipPostalCode],Len([shipPostalCode])-3) & " " & Right([shipPostalCode],3), shipPostalCode)

 

[nanscombe]

It's not a simple one. I'll give some thought to it in a little while.

In the mean time, here is a guide to the valid types of UK Post Code.

Format Example
A9 9AA [M1 1AA]
B, E, G, L, M, N, S, W postcode areas

A99 9AA [B33 8TH]

AA9 9AA [CR2 6XH]
All postcode areas except B, E, G, L, M, N, S, W, WC

AA99 9AA [DN55 1PT]

A9A 9AA [W1A 1HQ]
E1W, N1C, N1P, W1 postcode districts (high-density areas where more codes were needed)

AA9A 9AA [EC1A 1BB]
WC postcode area; EC1–EC4, NW1W, SE1P, SW1 postcode districts (high-density areas where more codes were needed)

What I would try to do is come up with a function which converts any given string to a series of 'A's and '9's and format it according the above rules.

 

[jdraw]

I agree that UK post codes are/can be complex. However, the question asked was specific to adding a space in a specific location, and not adding a space if one already existed.

Have you identified exactly what it is you want to solve? As namscombe has identified, there is much more to UK post codes.

You may have to test the Length of a postCode, exceptions etc in order to achieve what you want/need. But before you can solve it, you must clearly understand WHAT it is you want to do.

 

[wmphoto]

I think what the OP wants to do is standardise a non-standardised list of postcodes,

so that BL13 5NR would appear as BL13 5NR, but
BL135NR would be changed to BL13 5NR without also changing
BL13 5NR to BL13 5NR. (I intended to put a double space in there, but this forum is too clever by half and 'corrects' it each time)

I know for a fact that the Ordnance Survey's list of Postcodes is inconsistent in whether or not it puts the space in.

This is complicated by the fact that the bit before the space varies in both length and mix of letters and numbers, but the OP has already solved this issue, it's just making sure that duplicate spaces aren't inserted in postcodes which already have the space.

I think you're on the right lines with

Left([Postcode],Len([Postcode])-3) + " " + Right([Postcode],3)

To remove any spaces which may already exist in the postcode first, you will need to do the following

RTrim(Left([Postcode],Len([Postcode])-3)) + " " + Right([Postcode],3)

The RTrim function will remove any spaces which follow the first chunk

 

[nanscombe]

RTrim(Left([Postcode],Len([Postcode])-3)) + " " + Right([Postcode],3)

The RTrim function will remove any spaces which follow the first chunk

That should work nicely. Being a bit paranoid I was trying to think if there was any way it could be entered with a space at either end of the string?

But that could be avoided by running a Trim() over the entire Postcode first.



I must admit, I do get a bit carried away because I actually enjoy coding in VBA.

I have been known to start coding and think "Hang on, there's an easier way to do this "

 

[KenshiroUK]

Thanks! Cheers fella

 

[wmphoto]

That should work nicely. Being a bit paranoid I was trying to think if there was any way it could be entered with a space at either end of the string?

But that could be avoided by running a Trim() over the entire Postcode first.



I must admit, I do get a bit carried away because I actually enjoy coding in VBA.

I have been known to start coding and think "Hang on, there's an easier way to do this "

Haha yeah, I know next to nothing about coding. But I did have a similar issue with UK postcodes before using data from the OS which as mentioned, sometimes has spaces and sometimes doesn't. My issue was doing the reverse of what the OP is doing, starting with a nice clean postcode, with separate fields for before the space (outbound) and after the space (inbound), and combining them so they match the OS data.

 

[nanscombe]

Was that the PAF database? Postcode Address file?

I think I had to do something with that a few years ago. Taking a set of addresses once a month to Geocoding them if I remember correctly.

 

[wmphoto]

It's OS Codepoint, http://www.ordnancesurvey.co.uk/oswebsite/products/code-point-open/index.html