Use Replace function to reduce postcode to postcode region

[mark@collectiveid.co.uk]

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?

[Paradigm]

Re: Use Replace function to reduce postcode to postcode region

Use INSTR to find the first space and them use LEFT to trim the string to
the position of the first space.

<mark@collectiv eid.co.ukwrote in message
news:1168963299 .358671.295020@ v45g2000cwv.goo glegroups.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?
>

[mark@collectiveid.co.uk]

Re: Use Replace function to reduce postcode to postcode region

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(VarPost code, [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.
>
<mark@collectiv eid.co.ukwrote in message
news:1168963299 .358671.295020@ v45g2000cwv.goo glegroups.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?

[Marshall Barton]

Re: Use Replace function to reduce postcode to postcode region

mark@collective id.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