471,355 Members | 1,737 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

Use Replace function to reduce postcode to postcode region

I have a table of UK companies whose records I want to filter using a
map of postcode regions.

For the benfit of people outside the UK, our postcodes are a pain to
work with because they are not a standard length, and the part which
identifies the region isn't a standard length either.

The first letters of the postcode denotes a general region, and then
sub-regions are denoted by the following numbers and letters.

Thus a valid postcode could be

GL24 6TY
G1 4TH

Which means you can't simply Trim Left to the first two characters and
then filter the results on a region code of, say "G", because you would
be returning results from Glasgow and Gloucester.

If I can express each postcode in my table as a region (that is, the
first letters of the postcode only), I could then filter this query
using variables passed from my map (the variables will simply be the
postcode region letters, eg. G, L, M, PL, ME.

To do this I guess I need to take everything from the first occurrence
of a number in my postcode and get rid of it.

Is there a way of using the Replace function in an Access query to do
this?

Jan 16 '07 #1
3 2821
Use INSTR to find the first space and them use LEFT to trim the string to
the position of the first space.

<ma**@collectiveid.co.ukwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...
I have a table of UK companies whose records I want to filter using a
map of postcode regions.

For the benfit of people outside the UK, our postcodes are a pain to
work with because they are not a standard length, and the part which
identifies the region isn't a standard length either.

The first letters of the postcode denotes a general region, and then
sub-regions are denoted by the following numbers and letters.

Thus a valid postcode could be

GL24 6TY
G1 4TH

Which means you can't simply Trim Left to the first two characters and
then filter the results on a region code of, say "G", because you would
be returning results from Glasgow and Gloucester.

If I can express each postcode in my table as a region (that is, the
first letters of the postcode only), I could then filter this query
using variables passed from my map (the variables will simply be the
postcode region letters, eg. G, L, M, PL, ME.

To do this I guess I need to take everything from the first occurrence
of a number in my postcode and get rid of it.

Is there a way of using the Replace function in an Access query to do
this?

Jan 16 '07 #2
Thanks for the reply.

Not sure how that would work, given that there can be different numbers
of characters before the space eg:

G2 6TH
G12 7NY
GL14 6TY

If I find the space and then trim one character to the left, I would
get in the above case, G, G1 and GL1, when I really want to get to G,
G, GL.

I thought maybe something like:

Replace(VarPostcode, [some syntax that means 'anything numerical' +
everything that follows it],"")
Paradigm wrote:
Use INSTR to find the first space and them use LEFT to trim the string to
the position of the first space.

<ma**@collectiveid.co.ukwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...
I have a table of UK companies whose records I want to filter using a
map of postcode regions.

For the benfit of people outside the UK, our postcodes are a pain to
work with because they are not a standard length, and the part which
identifies the region isn't a standard length either.

The first letters of the postcode denotes a general region, and then
sub-regions are denoted by the following numbers and letters.

Thus a valid postcode could be

GL24 6TY
G1 4TH

Which means you can't simply Trim Left to the first two characters and
then filter the results on a region code of, say "G", because you would
be returning results from Glasgow and Gloucester.

If I can express each postcode in my table as a region (that is, the
first letters of the postcode only), I could then filter this query
using variables passed from my map (the variables will simply be the
postcode region letters, eg. G, L, M, PL, ME.

To do this I guess I need to take everything from the first occurrence
of a number in my postcode and get rid of it.

Is there a way of using the Replace function in an Access query to do
this?
Jan 16 '07 #3
ma**@collectiveid.co.uk wrote:
>I have a table of UK companies whose records I want to filter using a
map of postcode regions.
[snip]

I tried to answer this an another newsgroup.

Please don't multipost questions, it just wastes your time
and can make it difficult for the folks that are trying to
answer your questions. See
http://www.mvps.org/access/netiquette.htm
for suggestions on how to use newsgroups effectively.

--
Marsh
Jan 16 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by f pemberton | last post: by
37 posts views Thread by Ajai Jose | last post: by
6 posts views Thread by ssecorp | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.