Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    split a column into two seperate columns

    I was wondering if it possible to use an expression in a query to split one column in a table into two seperate columns. Column X is Description and looks like this, for example....


    2,500 @ $21.55. The @ symbol is always present so I essentially want to wind up with two columns Quantity (2,500) and Price @21.55
    Thanks in advance for any help.

  2. #2
    Join Date
    Jul 2008
    Location
    Alexandria, Egypt.
    Posts
    38
    Dear nybanshee,

    I used to do this exercise using MS Excel

    Try the following steps in MS Excel:

    Open the table in MS Access
    Press Ctrl+A to select all records
    Press Ctrl+C to copy all records
    Open MS Excel
    Press Ctrl+V to past all records
    Select the target column
    Press Ctrl+X to cut the column
    Go to the last column at right side
    Right click
    Choose insert cut cells
    Select the column
    Press data menu
    Choose Text to columns
    You can choose either delimited or fixed width
    Choose delimited
    Tick other and write @ in the cell
    Press finish


    I will try to do this in MS Access
    If I found it I will send it to you

    Thanks

    Ahmed

  3. #3
    Join Date
    Aug 2008
    Posts
    8
    The InStrRev function can split the column for you

    Hear is an example for updating First and last names in a table where the Full name has a space between the first and last in a seperate field. Change the " " parameter to "@" for your needs.

    UPDATE MyTable SET MyTable.FirstName = Mid([MyTable].[Fullname],InStrRev([MyTable].[FullName],"@")+1), MyTable.FullName = Mid([MyTable].[FullName],1,InStrRev([MyTable].[FullName]," ")-1);

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-19-2008, 02:19 AM
  2. Split text field into two text fields
    By Grant in forum Access
    Replies: 6
    Last Post: 01-31-2008, 05:52 AM
  3. Add Columns to query
    By 4petessake in forum Access
    Replies: 0
    Last Post: 06-15-2007, 01:38 PM
  4. Replies: 0
    Last Post: 02-14-2007, 01:35 PM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums