Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Seperating data in a field (2003)

    I created an Access database from information exported via a comma delimited file. It's "client" information, name, address, phone, etc. I want to seperate out the phone number field. The field contains informaiton that looks like "(503)222-2222". Some fields have nothing and some fields have "( ) - ". Notice there is no space between "(503)" and the number.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seperating data in a field (2003)

    Can you describe what exactly you mean by "separate out the phone number field"? Thank you.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating data in a field (2003)

    Sure: I want to move the area code to a field of it's own and want to get rid of the "( )" altogether

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seperating data in a field (2003)

    Create an area code field in the table.
    Then make a backup copy of the database, just to be safe for if something goes wrong.
    Next, create a query based on the table.
    Add the phone number and area code fields.
    Select Query | Update Query.
    In the 'Update to' line for the area code field, enter

    IIf(IsNull([Phone]) Or Left([Phone],5)="( )",Null,Mid([Phone],2,3))

    where Phone is the name of the existing phone number field.
    In the 'Update to' line for the phone number field, enter

    IIf(IsNull([Phone]) Or Left([Phone],5)="( )",Null,Right([Phone],8))

    where Phone again is the name of the existing phone number field.
    Select Query | Run to update the values.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating data in a field (2003)

    OK, I'll try it right now.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating data in a field (2003)

    PERFECT! Thank you for saving me hours of work!

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating data in a field (2003)

    Step 2 in this same database. It has a "Last Name field" that not only has last names like "Adams" or "Jones", but also Company names. What I'd like to do is add a Company Name field. Then run an update query that does 1) check the Last Name field-- if there is more than one "object" or "name", copy the name to the Company Name field. If there is only one Name or Object, just leave it alone. Is that possible?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seperating data in a field (2003)

    Is the person name always the first item in the last name field?
    Can there be persons with a double last name ("Featherstone Haugh")?
    Can there be company names consisting of two or more words ("Apple Computer")?

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating data in a field (2003)

    Actually, I figured out an easier way to do it. After studying the data further, I decided to use an Update query to 1) search for any First Name Field that was NULL. Then, for each record with an a First name field that was NULL, I copied the last name field to Company Name field. Out of 851 records, it appears (at first glance) that I only have a few that didn't fit....

Posting Permissions

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