Re: Use Replace function to reduce postcode to postcode region
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Thu, 18 Jan 2007 09:36:33 -0600
Gelatinous_Blob wrote:
Thanks Marsh, that looks like a neat solution...
Happy to help.
[apols for double posting BTW]
Live and learn.
--
Marsh
MVP [MS Access]
Marshall Barton wrote:.
mark@xxxxxxxxxxxxxxxxxx wrote:
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?
No. Replace can not do that.
If the region part is always just one or two alphabetic
characters, you could use:
IIf(postcode LIKE "?#*", Left(postcode,1), Left(postcode,2))
OTOH, if the region letters can be three or more characters,
the expression will quickly become very messy. If it gets
too messy to be practical, I suggest that you create a user
defined function to deal with the ideosyncracies.
- References:
- Use Replace function to reduce postcode to postcode region
- From: mark
- Re: Use Replace function to reduce postcode to postcode region
- From: Marshall Barton
- Re: Use Replace function to reduce postcode to postcode region
- From: Gelatinous_Blob
- Use Replace function to reduce postcode to postcode region
- Prev by Date: RE: multi calculations
- Next by Date: text to number format or preceeding 0
- Previous by thread: Re: Use Replace function to reduce postcode to postcode region
- Next by thread: Re: Use Replace function to reduce postcode to postcode region
- Index(es):
Relevant Pages
|
