Results 1 to 3 of 3

Thread: update cell

  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: update cell

    hello,

    i have approx 1500 records and realized that i should have put an input mask in the post code field.

    I have done this now and it appears to have populated all records. however the odd thing is that when i display this field in VB or merge it to word the format does not appear.

    in Access it looks like nn19 0eg and in VB or word looks like nn19oeg. If I create a new record then it is displayed correctly in all apps.

    any ideas how to update old fields.

    thanks in advance

    neil

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Hi Neil,

    I did something similar a while ago.

    I made a update query based on the length of the postcodes.

    I found that not all postcodes were NN19 0EG especially in London.

    eg; EC1 Clerkenwell/City, EC2 Tower of London/City

    So if the post code was 6 digits in length (BH14EX) the query had this

    expr1:iif(len(postcode)) = 6,left(postcode,3) & " " & right(postcode,3),"")

    if it was 7 digits (BH149EX)

    expr1:iif(len(postcode)) = 7,left(postcode,4) & " " & right(postcode,3),"")

    BH14 9EX

    I have also attached a list of the first part of every post code for each Town, Districts in the UK, think theres 27,000+ It might help you.

    If you do have major problems let me know, and I'll try to sort it for you, I know mine was a hugh pain to do...
    Attached Files Attached Files
    Last edited by Ken_Hart; 02-27-04 at 05:15.

  3. #3
    Join Date
    Oct 2003
    Posts
    58
    Originally posted by Ken_Hart
    Hi Neil,

    I did something similar a while ago.

    I made a update query based on the length of the postcodes.

    I found that not all postcodes were NN19 0EG especially in London.

    eg; EC1 Clerkenwell/City, EC2 Tower of London/City

    So if the post code was 6 digits in length (BH14EX) the query had this

    expr1:iif(len(postcode)) = 6,left(postcode,3) & " " & right(postcode,3),"")

    if it was 7 digits (BH149EX)

    expr1:iif(len(postcode)) = 7,left(postcode,4) & " " & right(postcode,3),"")

    BH14 9EX

    I have also attached a list of the first part of every post code for each Town, Districts in the UK, think theres 27,000+ It might help you.

    If you do have major problems let me know, and I'll try to sort it for you, I know mine was a hugh pain to do...
    Hello Ken,

    thank you very much indeed for your detailed help. I shall play with it tonight.

    Have a good weekend.

    Neil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •