Query help

[WembleyBear]

This should be easy but I'm having a problem with it.

I have a query which returns a list of address from the Targets table. One
of the fields holds the UK postcode, eg LU10 1NN, PE17 1DP.

Another table called Area holds the first part of the postcode ranges which
fall into our sales area, eg LU9, LU10, PE17 etc

What I want is for the query to return only records in our sales area (i.e.
records whose postcodes start with one of the postcode areas from the Area
table Ordinarily, I could just do Like "LU10*" to return all postcodes that
begin with LU10 etc if there were only a couple. But there's over 200.

What's the best way of doing this?

Martyn

 

[MGFoster]

This should be easy but I'm having a problem with it.

I have a query which returns a list of address from the Targets table. One
of the fields holds the UK postcode, eg LU10 1NN, PE17 1DP.

Another table called Area holds the first part of the postcode ranges which
fall into our sales area, eg LU9, LU10, PE17 etc

What I want is for the query to return only records in our sales area (i.e.
records whose postcodes start with one of the postcode areas from the Area
table Ordinarily, I could just do Like "LU10*" to return all postcodes that
begin with LU10 etc if there were only a couple. But there's over 200.

What's the best way of doing this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably using InStr() and Left():

WHERE Area.sales_area = Left$(Targets.postal_code,
InStr(Targets.postal_code, " ")-1)

This will compare the "LU9" to the left of a postal code of "LU9 1NN" as
an example. The InStr() finds the space and gives the correct length
for the Left$() function to read into the postal code.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdTdKIechKqOuFEgEQK38wCeIKHLcR6jf8Y1AxkO4JlGW5Aa2xQAoPRO
B5kcUZcNJqPMnfDNHEJZb4D9
=qiRD
-----END PGP SIGNATURE-----

 

[Software-Matters]

Martyn,

You say the postcodes are grouped in to areas, do you not have an area type
that you can search on?

--
<a
href="http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>