By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

Insert a space into postcode

P: n/a
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
Jan 17 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"Alan" <no****@nospam.com> wrote in message
news:dq**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
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


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

P: n/a

"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
news:fx******************@newssvr13.news.prodigy.c om...
"Alan" <no****@nospam.com> wrote in message
news:dq**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
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


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


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)
Jan 17 '06 #3

P: n/a
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" <no****@nospam.com> wrote in message
news:dq**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
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

Jan 17 '06 #4

P: n/a

"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
news:fx******************@newssvr13.news.prodigy.c om...
"Alan" <no****@nospam.com> wrote in message
news:dq**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
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


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

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)

Jan 17 '06 #5

P: n/a
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


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

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)


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/down...codeformat.pdf

Thanks again!

Alan
Jan 18 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.