Solved: Moving postcodes of varying lengths from an address field on MS Access db

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Stang62

Thread Starter
Joined
May 6, 2010
Messages
16
At work I use MS Access 2003 running on Windows XP. I have an address field which is populated by the last line of an address then a space followed by a UK postcode. I want to remove just the postcode from the field that it is in at present, and move it to a field where it can stand alone. The postcodes are all at the end of the field but the problem I have is that they are in differing formats/lengths eg SY10 7AG, N1 7BJ, LE1 7QA so I am finding it difficult to know which command to use to sort of 'cut and paste' the codes into their own field... can anyone help please :)
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Stang62, welcome to the Forum.
That is a tricky question, what currently separates the postcode from the rest of the address?
A Comma, or just a space?
 

Stang62

Thread Starter
Joined
May 6, 2010
Messages
16
Thanks, the postcodes are separated mostly by a space but there are a few separated by a comma
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I have to go now, I will discuss with you how to do it tomorrow.
How many Records need processing?
 

Stang62

Thread Starter
Joined
May 6, 2010
Messages
16
Sorry OBP I didn't get straight back to you, I'm just trying to get used to using this site too :). I have about 6,500 records to process and I've just checked.. there may be as many commas as spaces separating the address from the postcode. Thank you for trying to help me so far.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
This will probably take 2 or 3 runs to complete.
First of all create a normal select query with your Postcode Field in it.
If you have blank Addresses then also add the Address field to the query as well.
In design mode if you have blank Addresses then in the first Criteria Row of the Address column add
Not Is Null
On the Main menu click on Query>Update
In the new row that appears called "Update To" enter the following in the Post Code column
right([Address], 7)
where Address is the real name of the field that has the address & postcode in.
Save the Query and then run it.
 

Stang62

Thread Starter
Joined
May 6, 2010
Messages
16
Hi again OBD, I tried what you suggested (a great help) and it almost gets me to the result that I am looking for. A bit more background info - my address table has 5 address line fields and a postcode field which are regularly extracted from an inhouse library system. unfortunately c6500 postcodes from a legacy system have been tagged on to the end of address line 5 and are now required to be moved.
I managed to get a previous attempt of my own to a similar stage by using the' right' function but I used the number 8 (I am very new to these functions and self taught so i thought i may be misunderstanding how to use them and i took much longer than your suggestion to achieve it!) . The longest postcode= 8 characters including the middle space, the shortest postcode= 6 characters (including the middle space) so by using the number 7 the query leaves the first postcode character behind on some (eg. 'Berkshire RG40 3GA' returned 'G40 3GA' ) When I used the 8 it copied one or two characters from the end part of the address on some (eg. 'Glasgow, G1 1XQ' returned ', G1 1XQ' and 'London W9 2QP' returned 'n W9 2QP' ) The other problem I have when I achieve this part, is how to cut the portion I'm moving rather than copying it, leaving me with a pstcode in 2 of my address fields. I'm really appreciating your help and patience :)
 

OBP

Joined
Mar 8, 2005
Messages
19,895
OK, did you go with the 8 Characters then?
If so that is phase 1 to get the whole post code in to all the Records that have one.
Now we need to trim the data that is left, please read my whole post before doing anything and any questions that need answers before running anything.
Go back to your Update query abd reselect "Select Query" instead of update.
in a new column header enter
test: iif(left([Post Code], 1) = "," , 1,0)
if my syntax is correct it should put a 1 in any record where the first character is a , and 0 when it is not.
Assuming [Post Code] is your field name of course.
If that works Ok we then create a new Query based on that one which will be an Update Query and use the Criteria for the test column of 1, so any record with a one in we will reduce the character count by 1 with
Update To = right([Post Code],7)

We can then do the same check looking for the space with
test: iif(left([Post Code], 1) = " " , 1,0)

That should take care of most of the extra Characters, then we will deal with the 'London W9 2QP' returned 'n W9 2QP' by looking for the mid([Post Code], 2,1) = " " and run that, in fact it might be an idea to run that one first as it will find both a "n " and a ", " and remove them but no a "," on it's own.
When we have the correct Post code we will then run an update query to remove that number of Characters from the Address field.

You could copy the Record's Key ID field and the Post code to a new table and post that for me to work on if we can't get this to work properly on here.
 

Stang62

Thread Starter
Joined
May 6, 2010
Messages
16
I now have a postcode field containing perfect postcodes with varying lengths of 8, 7 and 6 characters. All I need to do now is trim these lengths from the end of the last address line.. ready for the next phase please :D
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Great. Take a back up copy of the database, or at least a copy of the main table.
So now create a select query with the key ID field and the Post code, add a column with
length: len([Post Code])
Call the query "Length", the query will list all the lengths of the Post codes.
now create a new query based on the main table with the Address field in it and then add the Length query to the query and join it to the table using key ID fields.
Change the the Query to an Update query and in the Update to row for the Address field enter
Left([Address Field Name],Len([Address Field Name])-[Length])
where Address Field Name is the name of your address field.
Run the query.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top