Results 1 to 9 of 9
  1. #1
    Petefured is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2008
    Posts
    16

    Splitting a field to separate fields

    Dear All,

    I have a table (lets call it inputtbl) in which I have a field that contains entries like:
    in record1: text1_text2_text3_text4
    in record2: text1_text2_text3
    etc

    I have created an outputtbl where I have inserted 4 fields to insert the "_" separated values of the inputtbl

    The problem is that I have difficulties with writing a code that splits the column of imputtbl to the columns in outputtbl

    I tried Split in VBA but I did not succeed

    Could you please help me?

    Many thanks in advance



    ps. I dont want to use Excel TextToColumn, this task needs to be carried out in Access in a way

  2. #2
    Patrick.Grant01 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    30

    Try something like the code below which I conveniently had to hand. A string is passed into the routine for splitting. This splits on the basis of commas not an underscore and it puts the values in an array. You need to decalre the array at the top of the module so the values are visible to other procedures...


    Sub MakeArrayFromList(str As String)
    Dim strChar As String
    Dim lngStringLength As Long, lngNoOfElements As Long
    Dim i As Long
    Dim j As Long

    'Count Elemenst in List
    lngStringLength = Len(str)
    i = 1
    lngNoOfElements = 0
    For i = 1 To lngStringLength
    strChar = Mid(str, i, 1)
    If strChar = "," Then
    lngNoOfElements = lngNoOfElements + 1
    End If

    Next i
    lngNoOfElements = lngNoOfElements + 1

    ReDim Preserve strArray(lngNoOfElements)
    'Clear array
    For i = 1 To UBound(strArray)
    strArray(i) = ""
    Next i
    lngStringLength = Len(str)
    i = 1
    j = 1
    For i = 1 To lngStringLength

    strChar = Mid(str, i, 1)
    If strChar <> "," Then
    strArray(j) = strArray(j) + strChar
    Else
    j = j + 1
    End If


    Next i
    i = 100
    End Sub

  3. #3
    Petefured is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2008
    Posts
    16
    Hello,

    I have another code to split the field (which seems a bit simpler to me) but the problem with that is it does not split the field to new fields, it just splits them to rows
    Could you please have a look at it?

    Sub Splitter()
    Dim db As DAO.Database
    Set db = Application.CurrentDb
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("input")
    Dim ary
    ary = Split(rs!entname, "_")
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
    Debug.Print ary(0)
    Debug.Print ary(1)
    Debug.Print ary(2)
    Debug.Print ary(3)
    rs.MoveNext
    Loop
    End Sub

    So I would like the new values to be put in separate fields in an existing table

    Many thanks for your help

  4. #4
    Patrick.Grant01 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    30

    You Need to open a second recordset

    The code does look simpler. Now you have separated the values out you need to open a 2nd recordset that finds the right row in the table and update it. So you need to do something like this:

    Dim strSQL as String
    strSQL = "Select From UpdateTable Where FieldX = ' " & strFindName & " ' "

    Dim rstUpdate As ADODB.Recordset
    Set rstUpdate = New ADODB.Recordset

    rstUpdate.ActiveConnection = CurrentProject.Connection
    rstUpdate.CursorType = adOpenKeyset
    rstUpdate.LockType = adLockOptimistic
    rstUpdate.Source = strSQL
    rstUpdate.Open

    rstUpdate!Field1=ary(0)
    rstUpdate!Field2=ary(1)
    rstUpdate!Field3=ary(2)
    rstUpdate.Update

    rstUpdate.Close
    Set rstUpdate = Nothing

  5. #5
    Petefured is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2008
    Posts
    16
    Hello,
    This looks really nice, I just dont know what I should write to strFindName, do I have to declare that as well upfront?

    Many thanks

  6. #6
    Patrick.Grant01 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    30
    The table you intend to update must have a unique key / field to identify the right record to update. You need to find out what that field is.....if you open the table in designer, one or more fields will have a key next to them.

  7. #7
    Petefured is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2008
    Posts
    16
    What if I choose not to update another table, but to insert new fields to the current table (Set rs = db.OpenRecordset("input"))
    I would add 4 new fields and then insert the output of Split to them
    I tried it with one field only to test but I got the error message that the table is locked by another procedure

    Here is the code I tried:

    Sub Splitter()
    Dim db As DAO.Database
    Set db = Application.CurrentDb
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("input")
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim ary
    ary = Split(rs!entname, "_")
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
    Rem Debug.Print ary(0)
    Rem Debug.Print ary(1)
    Rem Debug.Print ary(2)
    Rem Debug.Print ary(3)
    rs.MoveNext
    Loop
    Set tdf = db.TableDefs("input")
    Set fld = tdf.CreateField("TEST", DB_TEXT)
    tdf.Fields.Append fld
    rs!test = ary(0)
    Rem rstUpdate!Field2 = ary(1)
    Rem rstUpdate!Field3 = ary(2)
    rs.Update

    End Sub

    Could you please advise?
    Many thanks in advance

  8. #8
    Patrick.Grant01 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    30
    You open the recordset differently.

    Make strSQL = "SELECT * From TableX" 'No WHERE Clause

    The use something like:

    rstUpdate.AddNew
    rstUpdate!Field1=ar(0)
    rstUpdate!Field2=ar(1)
    rstUpdate.Update

    rstUpdate.Close
    set rstUpdate = nothing

    Are you really sure you want to add a new row though????

  9. #9
    Petefured is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2008
    Posts
    16

    Re:

    No actually I would not like to add a new row, I would like to add as many new fields to the input table as many output Split makes and fill them in with the output of Split

    Maybe I did not make myself clear right from the beginning

    I attach a screenshot of what I am talking about

    Thank you very much in advance

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

Similar Threads

  1. multiple fields in separate records
    By Fredo0709 in forum Database Design
    Replies: 9
    Last Post: 04-09-2010, 12:23 PM
  2. Replies: 0
    Last Post: 06-03-2009, 10:25 PM
  3. Separate one field into many
    By ellen in forum Programming
    Replies: 5
    Last Post: 12-22-2008, 06:01 PM
  4. problem in splitting Date and time fields
    By swaroop1012 in forum Queries
    Replies: 1
    Last Post: 11-22-2008, 11:29 AM
  5. Replies: 1
    Last Post: 10-26-2007, 07:29 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