Solved How to trim postcode.. (1 Viewer)

[Number11]

Hi, so i need to tri down the postcodes from a table so they match another table the issue is that one table has the full postcode like AB1 8TT and the other table just has AB. I think trim maybe the answer but unsure if that will work?

 

[arnelgp]

where do you need it?

you can match using the shortest postcode.
using query:

select short_table.postcode, wide_table.postcode as postcode2 from short_table inner join wide_table on short_table.postcode = left(wide_table.postcode, len(short_table.postcode));

 

[Number11]

using this

PostcodeRev: Left([Postcode],2)

 

[gemma-the-husky]

Some musings

It's tricky depending on what is in your "trimmed" postcode table, and the post code you have to check.
Does the selected post code affect things like a radial cost rate/distance, so you need to get the right one.

I mean for AB - Aberdeen, you might have these postcodes
AB
AB1
AB2
...
AB11
AB12

Assuming you have a full post code to check
a) does it have a space , in which case you can take the chars up to the space
b) if not, maybe take the length all but the last 3 chars.
(I think UK codes have a left part, and than always NXX for the last three chars, but I am not sure)

you could start trimming chars from the post code until you find a match
So - given a full postcode such as AB114XZ test these

AB114XZ
AB114X
AB114
AB11 - stop at a match.

BUT there can be problems.
for a post code such as AB1 1ER, this will stop at AB1
whereas for the same code presented as AB11ER it stops at AB11 (hence suggestion b) above to pre-process the post code).

Everything can get tricky with "fuzzy"/badly formed data.

 

[gemma-the-husky]

using this

PostcodeRev: Left([Postcode],2)

see my previous. Note that this doesn't work correctly for Birmingham, Glasgow, Liverpool and maybe other UK cities.

 

[isladogs]

You are trying to retrieve the postcode area from the full postcode. This is the non-numeric part of the postcode before the space.
As Dave indicated this is usually two letters e.g. AB or BS but can be just one letter e.g. G, B etc
Similarly the numbers can be one or two numbers before the space ...and to make matters worse you can have e.g. EC1A....which breaks all the standard rules.

Retrieving what you want can be messy as a result.
If it helps, I can provide a table of all 125 UK postcode areas for you to compare against.

NOTE the easy bit is that after the space is ALWAYS a number followed by two letters

 

[Number11]

Some musings

It's tricky depending on what is in your "trimmed" postcode table, and the post code you have to check.
Does the selected post code affect things like a radial cost rate/distance, so you need to get the right one.

I mean for AB - Aberdeen, you might have these postcodes
AB
AB1
AB2
...
AB11
AB12

Assuming you have a full post code to check
a) does it have a space , in which case you can take the chars up to the space
b) if not, maybe take the length all but the last 3 chars.
(I think UK codes have a left part, and than always NXX for the last three chars, but I am not sure)

you could start trimming chars from the post code until you find a match
So - given a full postcode such as AB114XZ test these

AB114XZ
AB114X
AB114
AB11 - stop at a match.

BUT there can be problems.
for a post code such as AB1 1ER, this will stop at AB1
whereas for the same code presented as AB11ER it stops at AB11 (hence suggestion b) above to pre-process the post code).

Everything can get tricky with "fuzzy"/badly formed data.

oh yes you are correct it does not work when the post code is just B or G, how do i do that to get it to work?

 

[Number11]

You are trying to retrieve the postcode area from the full postcode. This is the non-numeric part of the postcode before the space.
As Dave indicated this is usually two letters e.g. AB or BS but can be just one letter e.g. G, B etc
Similarly the numbers can be one or two numbers before the space ...and to make matters worse you can have e.g. EC1A....which breaks all the standard rules.

Retrieving what you want can be messy as a result.
If it helps, I can provide a table of all 125 UK postcode areas for you to compare against.

NOTE the easy bit is that after the space is ALWAYS a number followed by two letters

oh so my method doesnt work for say G or B postcodes

 

[Minty]

As per Colin's post - this isn't quite a straightforward as you may initially think.
Take him up on giving you the list, when I created mine (years ago) it took a considerable amount of manual work after the initial automatic bits.

 

[gemma-the-husky]

So it depends what post codes you have in your post code table.

The best way is to start with all characters before the space - so instead of NN12 4XZ, you start with NN12. Now test NN12, then keep removing one character at a time until you get a match, so test NN12, NN1, NN, and now you should match to Nottingham (I think NN is Nottingham)
In some cases, you may be happy with NN, but some post code areas are very big - Devon, Cornwall, Scottish Counties, and you may need to pin it down to the numeric area.

If you don't have a space start by removing the last 3, and carry on from there. Make sure the post code supplied is generally Ok. ie it has 6 or 7 characters excluding the space. If there is a space there are/should be 3 characters after, otherwise it's not being presented as a valid post code.

I imagine @isladogs postcode list of 125 is just the letters.

eg Postcodes in Cornwall are TR1 through to TR27, so you may well need a way to distinguish TR1 from TR12, say, and you need more than the basic 125 post codes.

 

[Number11]

As per Colin's post - this isn't quite a straightforward as you may initially think.
Take him up on giving you the list, when I created mine (years ago) it took a considerable amount of manual work after the initial automatic bits.

Ok this is the working option
Left([PostCode],1) & IIf(Asc(Mid([PostCode],2,1))>=65,Mid([PostCode],2,1),"")

 

[Minty]

This page lists some regular expressions about the postcode districts https://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom
Links from that do list them all but not in a very useable format.

 

[Minty]

Excuse the double post - this covers all the postcodes I think https://osdatahub.os.uk/downloads/open/CodePointOpen

 

[gemma-the-husky]

Ok this is the working option
Left([PostCode],1) & IIf(Asc(Mid([PostCode],2,1))>=65,Mid([PostCode],2,1),"")

As long as you are happy to just identify the city, that might be OK. Although @isladogs point about London post codes is probably still an issue. As I say, if you need to pinpoint a location in a large post code area such as Cornwall or even Perth (PH for example includes a number of Islands). then you might still have an issue.

 

[Number11]

so this is trimming the postcode but when i then try to create a query to lookup the short postcode to my distribution company listing i am getting error "Data type mismatch in criteria expression, then when i click ok the query fields all do #Name?

 

[isladogs]

I obtain my postcode data from the Office for National Statistics (ONS). Its much more detailed than that available from the Ordnance Survey (OS) but as a consequence, is also a much bigger download. The website UK Postcodes (doogal.co.uk) is also extremely useful

In case its useful to anyone, I have separate data tables for all
1. 125 postcode areas e.g. AB, BN, BS. Note that as well as single letter postcode area such as B,L there is also one special case with 3 letters GIR
2. Postcode districts e.g. BS1, BS2...BS99
3. Postcode sectors e.g. BS25 1, BS25 5, BS25 9
4. Individual postcodes (approx 2.6 million in all)

You may find the attached info interesting. It shows a few of the features from my commercial app:
UK Postal Address Finder - Mendip Data Systems

 

[Number11]

so this is trimming the postcode but when i then try to create a query to lookup the short postcode to my distribution company listing i am getting error "Data type mismatch in criteria expression, then when i click ok the query fields all do #Name?

so guess error is due to the query having the expression, and its not licking it ?

 

[gemma-the-husky]

try this
sorry - unable to post a code fragment. I will try later.

Maybe you can't use mid() and asc() within a query. Use a function. You need to test for a blank postcode, and a bad single char postcode

Use a function. You need to test for a blank postcode, and a bad single char postcode

function postzone(postcode as string) as string
   postzone = Left([PostCode],1) & ... your concatenation code, which I can't post for some reason.
end function

then just put this in your query
=postzone([postcode])

 

[Number11]

try this
sorry - unable to post a code fragment. I will try later.

Maybe you can't use mid() and asc() within a query. Use a function. You need to test for a blank postcode, and a bad single char postcode

Use a function. You need to test for a blank postcode, and a bad single char postcode

function postzone(postcode as string) as string
   postzone = Left([PostCode],1) & ... your concatenation code, which I can't post for some reason.
end function

then just put this in your query
=postzone([postcode])

Thank you but same issue still getting the daa type mismatch in criteria expression

 

[Minty]

That means there is something dodgy in the criteria of your query, not the function.

Can you copy and paste the entire SQL from your query - no editing?