Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Question Unanswered: Break apart a text field in access

    I am getting data passed to me in one field that needs to be broken apart. The data is address, city, state and zipcode but its in one field that looks like this:

    1234 Main St/Anytown/CA/12345

    I am using ms access and is it possible to break apart this field into 4 separate fields?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Only if you know it will ALWAYS be in four parts with those seperators. What if there is no town? Is there still a "//"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    There will always be data in those fields because they are required.

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    what about Split()?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or import. The import wizard allows you to specify a special character as a field separator I think. You could try that.

    What I would be worried about is data like Unit 5/635 Main Street being in the first data element.

    What version Access?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    What I would be worried about is data like Unit 5/635 Main Street being in the first data element.
    Good Point.

    Check it:
    Code:
    WHERE (LEN(myfield) - 4) <> LEN(REPLACE(myfield, "/", ""))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    I am importing other fields also and they are separated by comma's so I can't do anything on the import so I just need to figure how to break it apart once I am in Access. I am using version MS Access 2002

  8. #8
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    what about Split()?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The simplest** method is to check the data (using the clause above) and, if you are happy, run eight queries.

    Code:
    UPDATE myTable
    SET add_field_1 = MID(myField, 1, INSTR(myField, 1, "/"))
    FROM myTable
    Code:
    UPDATE myTable
    SET myField = MID(myField, INSTR(myField, 1, "/"), LEN(myField))
    FROM myTable
    Code:
    UPDATE myTable
    SET add_field_2 = MID(myField, 1, INSTR(myField, 1, "/"))
    FROM myTable
    Code:
    UPDATE myTable
     SET myField = MID(myField, INSTR(myField, 1, "/"), LEN(myField))
     FROM myTable
    and so on. Test on sample data first of course - air code

    ** Not necessarily the best - it depends on your requirements.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2008
    Posts
    5
    How does that work? I checked the web but didn't find good examples.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jmahaffie
    what about Split()?
    Split is a great function but only for procedural stuff. This is, IMHO, best handled as a set.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're right mate, split is the way I'd go; the other lads are just trying to ensure that the OP considers all the data issues before plunging in expecting everything to work

    The Split() function allows you to turn a delimited string (you specify the delimiter) into an array; which is an ideal solution to this problem
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    The Split() function allows you to turn a delimited string (you specify the delimiter) into an array; which is an ideal solution to this problem
    How does JET SQL handle arrays Geroge?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I retract my last statement to a point; the set based method Poots described will undoubtebly be more efficient
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    How does JET SQL handle arrays Geroge?
    Depends where the data is coming from mate
    George
    Home | Blog

Posting Permissions

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