473,371 Members | 1,683 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,371 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 2921
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: f pemberton | last post by:
I have a string (xdata) and theres a newline after every 17 characters of the string. I was wondering how I can replace multiple substrings multiple times within a string? To put it another way,...
8
by: warezguy05 | last post by:
Hi I'd like to forward users to a 'thank-you' page after they've submitted a form. I used this code and it worked perfectly till yesterday; header("Location:...
37
by: Ajai Jose | last post by:
Hi , I work on an ARM processor based embedded system. Code is mainly in C language. The project has a huge source base. We are supposed to optimise it. Most datastructures are declared as static...
12
by: bb nicole | last post by:
I have do a update profile for company where they can update after view their profile, but it cannot function... I need to submit the whole job portal soon, but still got many problem in my coding,...
8
by: Joe Cool | last post by:
I need to map several columns of data from one database to another where the data contains multiple spaces (once occurance of a variable number or spaces) that I need to replace with a single...
0
by: Pinna | last post by:
Hiya, I am trying to select based on a range on postcode on a table. The field POSTCODE is a varchar2(8). The problem is when refinng on 'where postcode between 'PE1' and 'PE29'', because this is...
2
by: DMcN | last post by:
Hi, please can someone help with a PHP error. I am trying to get a form on my website up and running with a PHP script. I've tested a PHP file on the server (and checked with my host) and all is...
3
pezholio
by: pezholio | last post by:
Hi, I'm having a bit of trouble using Google Maps and the Google Ajax Search API, I'm trying to take a postcode, geocode it and then calculate the distance between the that postcode and a second...
6
by: ssecorp | last post by:
GvR wants to eliminate all the functional stuff (lambda map reduce filter) which kind of makes sense for Python, listcomprehensions are more efficient(at least as implemented inpython) from what i...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.