Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Unanswered: Deleting numbers (ZipCodes) out of a column.

    Its very simple.... i have a column named MAILADR3 which contains city, state, zip with spaces in between.

    Now, i can seperate the city and state, so all i really need is to simply delete all the zip codes on there (there written with the second zip coeds too for example *33840 8500*)

    Is there any way to do this? It can probably be done by a command that just deletes numbers in the column maybe?


    Thanks for the help.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'll want to use an update statement.

    Read up on the Len() and Left() functions. You'll be able to do what you want using those two functions properly in an update statement.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2004
    Posts
    29
    im not really familiar with that, i searched online for it too couldnt find anytihng on it
    shouldnt there be somesort of sql code i can plug into query in sql mode?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    yes, using the Len() and Left() functions within sql.

    Read up.

    ProTip: Try the help file.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2004
    Posts
    29
    alright, i tried to figure those functions out, and none of the really work for me.

    my columnds contain records like this:

    BROOKLYN NY 11203 5511
    MILTON MA 02186 2555

    and etcetera....

    i did figure out i could use the "right function for this"
    so it would use the right fuction with 9 characters to the right?

    am i on the right path? how would i write this sql into access query?

  6. #6
    Join Date
    Oct 2004
    Posts
    29
    can anyone help me with this? its very hard for me cuz i have no basic knowledge of VBA or SQL. im trying to learn it but i need to get this done.

    thanks a lot if you can help

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Are all the zip codes 10 digits?
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    Be very careful on how you cut the text. If EVERY SINGLE record has a 10-digit zip code, meaning no 90233 only, or no missing zip codes, then you can work on cutting off the last 11 characters. Otherwise, it'll be a manual process for the exceptions. You cannot look for the first or last number in the field in case you have an address with a number, such as 123 Elm St Apt #3, or a military address, or a PO Box.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by LisaChow
    Be very careful on how you cut the text. If EVERY SINGLE record has a 10-digit zip code, meaning no 90233 only, or no missing zip codes, then you can work on cutting off the last 11 characters. Otherwise, it'll be a manual process for the exceptions. You cannot look for the first or last number in the field in case you have an address with a number, such as 123 Elm St Apt #3, or a military address, or a PO Box.
    Yes ... But here's an evil thought: Why not test until you do not have a number???
    Basically:

    1) Traverse R to L until hit a space.
    2) Test if a number. If so Goto 1
    3) update record with shortened address.
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Oct 2004
    Posts
    29
    I modified the column so that everything ends with he 10 digit zip codes with no exceptions.

    The problem i have is, i do not know where to plug the code in, i dont even know if the code i have is right

    I got something like this from the web:

    Code:
    <% =Right("abcde fghij klmno pqrst uvwxyz", 13) %>

    Output:
    pqrst uvwxyz


    But i dont understand how that is supposed to be plugged into access?

  11. #11
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    What if you first determine the length of the string, then update the field to len-10?

    Something like this:

    strLeng = Len([FieldName])

    strKeep = Left([FieldName],strLeng-10)

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you sure all the data is so neat and clean?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by idioteque
    I modified the column so that everything ends with he 10 digit zip codes with no exceptions.
    phenomenal

    mind telling us how you did that?

    and please explain also why, while you were in there looking at every record, you didn't just also lop off the zip code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by r937
    phenomenal

    mind telling us how you did that?

    and please explain also why, while you were in there looking at every record, you didn't just also lop off the zip code
    BECAUSE he wants to do it this way ..... I must be thick because even I did not spot that he corrected the zip codes in order to strip them out ...

    Nice one Rudy!
    Back to Access ... ADO is not the way to go for speed ...

  15. #15
    Join Date
    Oct 2004
    Posts
    29
    hey thanks for all the help everyone i have yet to try those methods because im busy on something else but i will get back to you on that....how i made it so neat was pretty easy... there were only like four records that contained only the 6 digit codes so i just got rid of them

Posting Permissions

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