By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

postcode lookup

P: n/a
I have a field for a postcode. I'd like another field to auto contain an
'area' number, based on the first part (only) of the postcode. For example,
if a postcode LS4 4DJ was entered, another field would read the LS4 part and
enter '7' in that new field.

I have a table that contains an up to date list of just these first-part
postcodes...

Thanks,
Lap
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
I assume your second table (TblAreas) looks like
SmallPostCode Area
LS 7
CO 5
etc

Create a query (QSmallPostCode) based on your table containing the post
codes containing the following
SmallPostCode:IIf(Len([PostCode])>=2,Left$([PostCode],2),"")
This extracts the first 2 letters (nothing for a missing post code

Then create a second query based on TblAreas and QSmallPostCode with a left
join between the 2 SmallPostCode fields

See if that gets you on your way

Phil

"Lapchien" <cc****@nospamplease.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I have a field for a postcode. I'd like another field to auto contain an
'area' number, based on the first part (only) of the postcode. For example, if a postcode LS4 4DJ was entered, another field would read the LS4 part and enter '7' in that new field.

I have a table that contains an up to date list of just these first-part
postcodes...

Thanks,
Lap

Nov 12 '05 #2

P: n/a
Just use a case statement

Select Case Postcode
Case Trim(left(Postcode,3))="LS4"
Area = 4
Case Trim(left(Postcode,3))="LS5"
Area =5
Case.......
Area......
End Select

Patrick

On Mon, 1 Dec 2003 13:16:29 -0000, "Lapchien"
<cc****@nospamplease.eclipse.co.uk> wrote:
I have a field for a postcode. I'd like another field to auto contain an
'area' number, based on the first part (only) of the postcode. For example,
if a postcode LS4 4DJ was entered, another field would read the LS4 part and
enter '7' in that new field.

I have a table that contains an up to date list of just these first-part
postcodes...

Thanks,
Lap


Nov 12 '05 #3

P: n/a
Thanks - I'll let you know how I get on...
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:3f***********************@mercury.nildram.net ...
I assume your second table (TblAreas) looks like
SmallPostCode Area
LS 7
CO 5
etc

Create a query (QSmallPostCode) based on your table containing the post
codes containing the following
SmallPostCode:IIf(Len([PostCode])>=2,Left$([PostCode],2),"")
This extracts the first 2 letters (nothing for a missing post code

Then create a second query based on TblAreas and QSmallPostCode with a left join between the 2 SmallPostCode fields

See if that gets you on your way

Phil

"Lapchien" <cc****@nospamplease.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I have a field for a postcode. I'd like another field to auto contain an 'area' number, based on the first part (only) of the postcode. For

example,
if a postcode LS4 4DJ was entered, another field would read the LS4 part

and
enter '7' in that new field.

I have a table that contains an up to date list of just these first-part
postcodes...

Thanks,
Lap


Nov 12 '05 #4

P: n/a

In the AfterUpdate event of the postcode field Try:-

Me.AreaCode = Mid(PostCode, 1, InStr(PostCode, " ") - 1)
On Mon, 1 Dec 2003 13:16:29 -0000, "Lapchien"
<cc****@nospamplease.eclipse.co.uk> wrote:
I have a field for a postcode. I'd like another field to auto contain an
'area' number, based on the first part (only) of the postcode. For example,
if a postcode LS4 4DJ was entered, another field would read the LS4 part and
enter '7' in that new field.

I have a table that contains an up to date list of just these first-part
postcodes...

Thanks,
Lap


Nov 12 '05 #5

P: n/a
On Mon, 01 Dec 2003 17:12:33 GMT, pe********@mcmail.com wrote:
Sorry did not notice the "enter '7' in that new field"
'Presuming that LS4 is 7 in your area's Table

Try this

Private Sub PostCode_AfterUpdate()

Dim strAreaCode As String
strAreaCode = Mid(PostCode, 1, InStr(PostCode, " ") - 1)
AreaCode = DLookup("Areacode", "tblAreas", "postcode ='" & strAreaCode
& "'")
End Sub

In the AfterUpdate event of the postcode field Try:-

Me.AreaCode = Mid(PostCode, 1, InStr(PostCode, " ") - 1)
On Mon, 1 Dec 2003 13:16:29 -0000, "Lapchien"
<cc****@nospamplease.eclipse.co.uk> wrote:
I have a field for a postcode. I'd like another field to auto contain an
'area' number, based on the first part (only) of the postcode. For example,
if a postcode LS4 4DJ was entered, another field would read the LS4 part and
enter '7' in that new field.

I have a table that contains an up to date list of just these first-part
postcodes...

Thanks,
Lap


Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.