Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    7

    Split text field into two text fields


    I have a text field (part_#) that contains part numbers and descriptions. (123 part for machine) The part numbers are not all numbers, nor are they all same length. I want to make a new field named Description.
    What is the best way to leave the first set of text (up to the first space) in the part_# field and take the rest of the text (could be one word, or multiple words) and put them into a new field called Description? without loosing any relational connections?
    Thank you in advnce.
    Grant

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Don't know if there is a better way, but I would use VBA to update the table. Assuming that you have a field called 'Part_number' and another called 'Description' in a table called 'yourtable'...

    'Create a recordset that selects all of the records in the table.

    Dim db as database
    Dim rst as recordset
    set rst = db.openrecordset ("Select * from yourtable")

    'Cycle through each record and extract the information you want to use.

    Do while not rst.eof
    'Find the location of the space and extract the details to the left of it
    strPart = Left(rst![Part_number],Instr(rst![Part_number]," ")-1)
    'Find the location of the space and extract the details to the right of it
    strDescription = Mid(rst![Part_number],Instr(rst![Part_number]," ")+1)

    'Run an update query - Assume that there is a unique record identifier called Part_id
    Docmd.runsql "UPDATE yourtable SET [Part_number] = '" & strPart & "', [Description] = '" & strDescription & "' WHERE [Part_id] = " & rst[Part_id]

    'Complete the loop and close the recordset

    Loop
    rst.close

  3. #3
    Join Date
    Jan 2008
    Posts
    7
    Thank you for the quick responce. Your code looks good, I am not real experienced at this, but I will give it a try.

  4. #4
    Join Date
    Jan 2008
    Posts
    7
    Okay, I opened up the database, ctr+G to get to the code. I put in the code how it was written, and got this compile error; Invalid outside prodecure on this part Set rst = db.OpenRecordset("Select* from tblParts"). It had highlighted the word set.

    Here is the code;
    Option Compare Database

    'Create a recordset that selects all of the records in the table.
    Dim db As Database
    Dim rst As Recordset
    Set rst = db.OpenRecordset("Select* from tblParts")

    'Cycle through each record and extract the information you want to use.

    Do While Not rst.EOF
    'Find the location of the space and extract rthe details to the left of it
    strPart = Left(rst![part_#], InStr(rst![part_#], " ") - 1)
    'Find the location of the space and extract the details to the right of it
    strDescription = Mid(rst![part_#], InStr(rst![part_#], " ") + 1)

    'Run an update query-Assume that there is a unique record identifier called ID
    DoCmd.RunSQL "UDATE tblParts Set [Part_#]=" '&strPart&'",[Description]="'& strDescription&'"Where [id]= &rst[id]
    'Complete the loop and close the recordset

    Lemme know where I messed up. :?:

  5. #5
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Did you put in the 'Set db = CurrentDb' line before 'Set rst = db.OpenRecordset...' line?

    Also, be sure the apostrophes are inside the quotes

    ex. "UPDATE tblParts SET [Part_#] = ' " & strPart & " ', [Description] = ' " & strDescription & " ' WHERE [id] = " & rst![id]

    The expression will translate to

    UPDATE tblParts SET [Part_#] = 'thepartnumber', [Description] = 'thedescription' WHERE [id] = theidnumber

  6. #6
    Join Date
    Jan 2008
    Posts
    7
    No, I did not. I am pretty green at this, you may have to hold my hand a bit. I will try again.

  7. #7
    Join Date
    Jan 2008
    Posts
    7
    I got it finally. I used a formula from another board. Thank you for all your help!

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

Similar Threads

  1. How to count charcter or text in field
    By nshaikh in forum Queries
    Replies: 3
    Last Post: 09-12-2008, 10:27 AM
  2. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM
  3. Validation rule for a text field
    By wasim_sono in forum Forms
    Replies: 4
    Last Post: 03-14-2006, 11:39 PM
  4. Concatenate two fields (text & number) for key field
    By Larry Elfenbein in forum Forms
    Replies: 2
    Last Post: 11-10-2005, 07:45 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