Extract UK Postcode With Expression in Query (1 Viewer)

main-man

Registered User.
Local time
Today, 15:24
Joined
Oct 20, 2005
Messages
48
Hi

If i wanted to run a query from customer records in my database and wanted a list of specific postcodes that matched the criteria how would i do this?

For example each customer records has a postcode BD4 4KL, LS9 7YH ETC

I want to categorise each postcode set to see for example all the customers with postcodes begining with have ordered factsheets.

In the criteria part of the query how would i write it so it only extracts specific postcodes from the database from the ones i require

e.g "HU1, HU2, HU3 HU4" - checks database - returns all postcodes beginning with this.

Please help me!

Thanks
 

main-man

Registered User.
Local time
Today, 15:24
Joined
Oct 20, 2005
Messages
48
as in

criteria: (hu1*) (hu2*)

It doesnt work Col, could you provide me an example
 

ColinEssex

Old registered user
Local time
Today, 15:24
Joined
Feb 22, 2002
Messages
9,110
No, as in

Code:
Left([Postcode],3)

Use it in a query

Col
 

Mile-O

Back once again...
Local time
Today, 15:24
Joined
Dec 10, 2002
Messages
11,316
Need Help Immediately!

Excuse us while we drop everything to help you. :rolleyes:

Bear in mind that this is a forum where people volunteer to help others with their problems and as such they will give of their time as and when they can. Please, if you have a post, give it a relevant title pertaining to the problem rather than making it sound as if everyone should give up what they are doing to come to your aid. Immediately is not an option (only when you pay someone can you order them and expect something) so it's best just to be patient.

Now, although Col has given you a reply, there is a flaw in it. Not all postcodes' first part is three characters. Some have four.

You will need to find the space in the postcode and then use the Left() function.

i.e.

Code:
Left([PostCode], InStr(1, [PostCode], " ") - 1)

I have renamed this post to reflect the question.
 

main-man

Registered User.
Local time
Today, 15:24
Joined
Oct 20, 2005
Messages
48
I do apologise for the title!

Im no good with code and wouldnt no where to place it? Could you provide me with an example which i could paste in?

Ive placed this in the criteria section:

Like "hu1*" Or Like "hu2*" Or Like "hu3*"

This has grouped all the postcodes beginning with hu1, hu2 and hu3.

Would you say this is correct?
 

ColinEssex

Old registered user
Local time
Today, 15:24
Joined
Feb 22, 2002
Messages
9,110
Thanks SJ, I was going on the examples posted.:rolleyes:

Col
 

ColinEssex

Old registered user
Local time
Today, 15:24
Joined
Feb 22, 2002
Messages
9,110
main-man said:
Ive placed this in the criteria section:

Like "hu1*" Or Like "hu2*" Or Like "hu3*"

This has grouped all the postcodes beginning with hu1, hu2 and hu3.

Would you say this is correct?
Is it the result you wanted?

Col
 

main-man

Registered User.
Local time
Today, 15:24
Joined
Oct 20, 2005
Messages
48
hu1*" Or Like "hu2*" Or Like "hu3*"

It has returned the correct information but i have a problem because not all is wanted.

For example hu1* i wanted a list of postcodes e.g HU1 4th
HU1 8IK

this is the type of information i required but it also included other postcodes that began with HU1 for example HU10 8UJ, HU11 9KL.

what could i include to limit its output to HU1 only?
 

Bat17

Registered User.
Local time
Today, 15:24
Joined
Sep 24, 2004
Messages
1,687
"HU1 *"
Make sure you include the space

Peter
 

ColinEssex

Old registered user
Local time
Today, 15:24
Joined
Feb 22, 2002
Messages
9,110
or use the functions SJ and I provided as a calculated field in a query. If your field is called "Postcode" you can paste them direct into the query as a new (calculated) field

Col
 

main-man

Registered User.
Local time
Today, 15:24
Joined
Oct 20, 2005
Messages
48
Thank you ever so much mate, its worked!

p.s if i used the code u suggsted where would i place it?
 

Mile-O

Back once again...
Local time
Today, 15:24
Joined
Dec 10, 2002
Messages
11,316
Out of interest, are you physically putting these postcodes into the query?
 

main-man

Registered User.
Local time
Today, 15:24
Joined
Oct 20, 2005
Messages
48
Yes Col im physically puting the postcodes in the query
e.g

criteria: Like "hu1 *" Or Like "hu2 *" Or Like "hu3*" Or Like "ls1" etc
 

fertooos

New member
Local time
Today, 08:24
Joined
Dec 8, 2019
Messages
8
Hey. It is cool, but is there anything similar to find out not phone numbers but postcodes? I recently moved to England and I need a bristol postcode list, does anyone know how to find it out? I often need to order something from the Internet, but I just can’t enter the postcode! can you advise me anything?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 15:24
Joined
Jan 14, 2017
Messages
18,186
The link you provided already provides a list of all Bristol postcodes.
However if you want to have a saved list, you could look at my UK Postcode Finder app.
Its a free cut down version of one of my commercial apps
 

Users who are viewing this thread

Top Bottom