Insert a space into postcode

[Alan]

Hi,

I'm getting as bit confused with my queries. I simply (!) want to insert a
space into a postcode field the 4th character from the right, so, for
example, ML201TQ becomes ML20 1TQ, which is the post offices format.
Unfortunately I've been given a database with postcodes that don't have this
space.

Thanks

Alan

[Fred Zuckerman]

Re: Insert a space into postcode

"Alan" <nospam@nospam. com> wrote in message
news:dqjbkk$ctu $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
> Hi,
>
> I'm getting as bit confused with my queries. I simply (!) want to insert a
> space into a postcode field the 4th character from the right, so, for
> example, ML201TQ becomes ML20 1TQ, which is the post offices format.
> Unfortunately I've been given a database with postcodes that don't have[/color]
this[color=blue]
> space.
>
> Thanks
>
> Alan
>[/color]

Add a new field (column) to your query. Call it PostCode2.
Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
Fred Zuckerman

[Anthony England]

Re: Insert a space into postcode


"Fred Zuckerman" <ZuckermanF@sbc global.net> wrote in message
news:fxazf.1010 3$H71.2688@news svr13.news.prod igy.com...[color=blue]
> "Alan" <nospam@nospam. com> wrote in message
> news:dqjbkk$ctu $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=green]
>> Hi,
>>
>> I'm getting as bit confused with my queries. I simply (!) want to insert
>> a
>> space into a postcode field the 4th character from the right, so, for
>> example, ML201TQ becomes ML20 1TQ, which is the post offices format.
>> Unfortunately I've been given a database with postcodes that don't have[/color]
> this[color=green]
>> space.
>>
>> Thanks
>>
>> Alan
>>[/color]
>
> Add a new field (column) to your query. Call it PostCode2.
> Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
> Fred Zuckerman[/color]



The trouble with UK postcodes is that they can vary in length and there are
a number of patterns which are valid. I believe that, although there might
be an odd exception, the one thing you can rely on is the three characters
at the end being Numeric, Alpha, Alpha such as '1TQ'.
So if you are sure the field holds valid uk postcodes which are simply
missing the space, then use Fred's suggestion, but modify the expression to:

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

[Terry Kreft]

Re: Insert a space into postcode

What you are saying is not correct.There are a whole range of postcodes
where the first part would be 3 chars not4 chars.

e.g HU9 0PB



--

Terry Kreft


"Alan" <nospam@nospam. com> wrote in message
news:dqjbkk$ctu $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
> Hi,
>
> I'm getting as bit confused with my queries. I simply (!) want to insert a
> space into a postcode field the 4th character from the right, so, for
> example, ML201TQ becomes ML20 1TQ, which is the post offices format.
> Unfortunately I've been given a database with postcodes that don't have[/color]
this[color=blue]
> space.
>
> Thanks
>
> Alan
>
>[/color]

[Anthony England]

Re: Insert a space into postcode


"Fred Zuckerman" <ZuckermanF@sbc global.net> wrote in message
news:fxazf.1010 3$H71.2688@news svr13.news.prod igy.com...[color=blue]
> "Alan" <nospam@nospam. com> wrote in message
> news:dqjbkk$ctu $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=green]
>> Hi,
>>
>> I'm getting as bit confused with my queries. I simply (!) want to insert
>> a
>> space into a postcode field the 4th character from the right, so, for
>> example, ML201TQ becomes ML20 1TQ, which is the post offices format.
>> Unfortunately I've been given a database with postcodes that don't have[/color]
> this[color=green]
>> space.
>>
>> Thanks
>>
>> Alan
>>[/color]
>
> Add a new field (column) to your query. Call it PostCode2.
> Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
> Fred Zuckerman[/color]


The trouble with UK postcodes is that they can vary in length and there are
a number of patterns which are valid. I believe that, although there might
be an odd exception, the one thing you can rely on is the three characters
at the end being Numeric, Alpha, Alpha such as '1TQ'.
So if you are sure the field holds valid uk postcodes which are simply
missing the space, then use Fred's suggestion, but modify the expression to:

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

[Alan]

Re: Insert a space into postcode

[color=blue][color=green][color=darkred]
> >> Hi,
> >>
> >> I'm getting as bit confused with my queries. I simply (!) want to[/color][/color][/color]
insert[color=blue][color=green][color=darkred]
> >> a
> >> space into a postcode field the 4th character from the right, so, for
> >> example, ML201TQ becomes ML20 1TQ, which is the post offices format.
> >> Unfortunately I've been given a database with postcodes that don't have[/color]
> > this[color=darkred]
> >> space.
> >>
> >> Thanks
> >>
> >> Alan
> >>[/color]
> >
> > Add a new field (column) to your query. Call it PostCode2.
> > Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
> > Fred Zuckerman[/color]
>
>
> The trouble with UK postcodes is that they can vary in length and there[/color]
are[color=blue]
> a number of patterns which are valid. I believe that, although there[/color]
might[color=blue]
> be an odd exception, the one thing you can rely on is the three characters
> at the end being Numeric, Alpha, Alpha such as '1TQ'.
> So if you are sure the field holds valid uk postcodes which are simply
> missing the space, then use Fred's suggestion, but modify the expression[/color]
to:[color=blue]
>
> Left([Postcode],Len([Postcode])-3) & " " & Right([Postcode],3)
>
>
>[/color]

Thanks Fred, Anthony and Terry,

Yes Anthony and Terry are correct as UK postcodes do vary in length but the
end is always consistent in that there is always a space at the 4th
character from the right as indicated by this document:

http://www.mrs.org.uk/standards/downloads/postcodeformat.pdf

Thanks again!

Alan