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.

MS Access: Parsing Multiple Text From Column

Discussion in 'Business Applications' started by lp092jl, Aug 10, 2006.

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

    lp092jl Thread Starter

    Joined:
    Sep 8, 2004
    Messages:
    18
    I have a column that has multiple text values within it.

    EX:

    Code:
    Field
    7864         0     99.4%          48           0      7912       402:50:16
    199         0     98.5%           3           0       202        23:41:17

    I would like to be able to separate each value into a separate column. After a bit of research I was able to find a parsing statement that would work for splitting up first and last names but I was unable to split up more than two values.

    EX:

    Code:
    FirstName: Right$([Name],Len([Name])- InStr(1,[Name],",")-1)
    
    LastName: Left$([Name],InStr(1,[Name],",")-1)
    Does anyone have any idea on how to successfully split up more than two values?
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    It's not clear what you want with the results. You want them in other columns? Or variables? Is this a one-time need, or do you have to do it regularly? Some mor info is needed here, because there are many possibilities, and the objective determines the method.

    If the operation is needed to be done only once, and the separated values go into new columns of the table, then the most simple way is:
    - Copy the multiple text column via clipboard to an excel sheet
    - Use excel's "Text to columns" wizard to separate it. It's in the Data menu.
    - Put the results in a new workbook, and save it as "something.xls".
    - In Access, open the table you want the data imported into.
    - Create the appropriate fields (columns).
    - Import data into the table. File/Import, as file type select *.xls, browse to "something.xls", click Import, follow wizard.

    If this is not what you want, well, almost any Access wish is satisfiable via VBA code, or perhaps other ways, too, but we need to know, what exactly you want.
     
  3. lp092jl

    lp092jl Thread Starter

    Joined:
    Sep 8, 2004
    Messages:
    18
    Hello Malacka,

    The query that I am developing is part of a scheduling process that I am wrapping up. The query that includes the grouped text needs to be seperated out and exported to an internal table (other columns).

    I already have the automatic scheduling completed, but I just needed to find a way to get the values into different columns in a table.

    I understand that for one time exports, the data can be toggled from exporting out of Access to a text or xls file and then re-imported/linked back into Access, but that is not what I need.

    It would be ideal if I can do it within a query to later append to a table.

    Any suggestions?
     
  4. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    The best way to do it is with VBA, I have already written this routine for someone else on the VBAX forum. The way that this is done is to create a form from your raw import data with the new fields (columns) on the form.
    The VBA steps through the records of data and parses the raw field data in to the new fields.
    I can point you to the Thread on VBAX or create the code for you.
     
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!

Loading...
Thread Status:
Not open for further replies.

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

  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