Update query problem

[fieldling]

I have a field [Postcode] and I need to put a space in between the characters. For example:
RG204QZ needs to be RG20 4QZ
L75HG nees to be L7 5HG.

As these are British postcodes the space is always before the third from last character. So I am using: Right([Postcode],3) but not sure how to put the space in.

I've tried examples I found on the forum but no luck. I'm sure the answer is staring me in the face, just can't see it! Any ideas?

Thanks

 

[KenHigg]

Something like:

NewPostCode = left([Poastcode],4) & " " & right([Poastcode],2)

???

 

[Brianwarnock]

More like

Left([postcode],len(postcode)-3) & " " & right([postcode],3)

Brian

 

[KenHigg]

Okie dokie...

 

[Brianwarnock]

Its Ok Ken you could not possibly realise the crazy design of our Postcodes.

Brian

 

[gemma-the-husky]

brian/fieldling

is that correct - a post code always/only has 3 characters to the right of the space?

 

[Brianwarnock]

As far as i know yes, but I could be wrong, if so the parsing for fielding will get real interesting.

Brian

 

[Rich]

brian/fieldling

is that correct - a post code always/only has 3 characters to the right of the space?

What if there's no space at all ie PL276JQ

 

[Brianwarnock]

What if there's no space at all ie PL276JQ

Are you saying that that is the real postcode, surely it is PL27 6JQ

Brian

 

[KenHigg]

(where did I put my goody's?)

 

[Rich]

Are you saying that that is the real postcode, surely it is PL27 6JQ

Brian

It gets there as posted, eventually

 

[Brianwarnock]

Comon Rich we are not discussing the quality of our postal service but in achieving correct postcodes.

Brian

 

[Brianwarnock]

(where did I put my goody's?)

 

[KenHigg]

 

[Rich]

Comon Rich we are not discussing the quality of our postal service but in achieving correct postcodes.

Brian

Surely it's the end result that matters and who writes the postcode with a space in?

 

[KenHigg]

Surely it's the end result that matters and who writes the postcode with a space in?

If it's proper then I'm guess the queen may

 

[fieldling]

Many thanks for your replies everyone and sorry for starting a debate about postcodes of all things!
And it's true, a british postcode only ever has 3 characters after the space. The post will probably still reach it's destination without it but I'm not giving Royal Mail any excuses...

 

[Brianwarnock]

Many thanks for your replies everyone and sorry for starting a debate about postcodes of all things!
And it's true, a british postcode only ever has 3 characters after the space. The post will probably still reach it's destination without it but I'm not giving Royal Mail any excuses...

You needn't apologise for starting the debate, Rich has strayed from the Watercooler where he frequently writes rubbish.

Brian