1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by Stang62, May 6, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Stang62

    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 :)
     
  2. OBP

    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?
     
  3. Stang62

    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
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Would that be a Comma and a space or just a comma?
     
  5. OBP

    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?
     
  6. Stang62

    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.
     
  7. OBP

    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.
     
  8. Stang62

    Stang62 Thread Starter

    Joined:
    May 6, 2010
    Messages:
    16
    Thanks, I'll have a go with that and let you know how I get on!
     
  9. Stang62

    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 :)
     
  10. Stang62

    Stang62 Thread Starter

    Joined:
    May 6, 2010
    Messages:
    16
    Sorry OBP not OBD!
     
  11. orangeCat1

    orangeCat1

    Joined:
    Dec 14, 2008
    Messages:
    24
    So, what you're saying is that the Post codes are not all the same length, correct?
     
  12. OBP

    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.
     
  13. Stang62

    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
     
  14. OBP

    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.
     
  15. Stang62

    Stang62 Thread Starter

    Joined:
    May 6, 2010
    Messages:
    16
    Done!! Thank you so much for your much appreciated help OBP :D
     
  16. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/921467

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice