Results 1 to 3 of 3
  1. #1
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13

    Question Help splitting a delimited field into new related records, Access 2013

    Hello,

    I've got a table (tblOriginal) that has a field


    [ListOfThings] which contains a space delimited field which I would like to break out into separate related records (ignoring when
    [ListOfThings] is empty):

    Code:
    tblOriginal
    
    [EventNumber]         [AnotherField]          
    [ListOfThings]
    1                        a                   a b c2 g6
    2                        b                   f3c g7 h
    3                        b                   
    4                        a                   c1 d e
    
    tblThings
    
    [EventNumber]         [AnotherField]            [AThing]
    1                       a                   a
    1                       a                   b
    1                       a                   c2
    1                       a                   g6
    2                       b                   f3c
    2                       b                   g7
    2                       b                   h
    4                       a                   c1
    4                       a                   d
    4                       a                   e
    I've tried to adapt the VBA code from the following post:
    https://www.accessforums.net/showthr...ted+field+loop

    Like so:

    Code:
    Private Sub Command101_Click()
    Dim varSplit As Variant
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim lngCount As Long
     
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Select [EventNumber], 
    [ListOfThings] FROM tblOriginal")
     
    With rst
     
    Do Until .EOF
       varSplit = Split(![EventNumber], " ")
       For lngCount = 0 To UBound(varSplit)
          strSQL = "INSERT INTO tblThings ( EventNumber, Thing ) " & _
                       "VALUES (!EventNumber, Chr(34) & varSplit(lngCount) & Chr(34));"
          CurrentDb.Execute strSQL, dbFailOnError
       Next
       .MoveNext
     
    Loop
     
    End With
     
    rst.Close
    Set rst = Nothing
    End Sub
    I'm ignoring the [AnotherField] for the moment to simplify things... But I get a Runtime Error '3085': Undefined function 'varSplit' in expression. And then Access highlights this line:

    CurrentDb.Execute strSQL, dbFailOnError

    I'm pretty new to Access. It appears that I need to define varSplit somehow, but I really don't have a clue. Any help will be greatly appreciated. You may have to write it in crayon.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are several things wrong in the code.
    Try this revised code:
    Code:
    Private Sub Command101_Click()
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim varSplit As Variant
        Dim lngCount As Long
    
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Select EventNumber, ListOfThings FROM tblOriginal")
    
        With rst
            Do Until .EOF
                If Len(Trim(!ListOfThings)) > 0 Then   '<<-- added - checks for NULLs
                    varSplit = Split(!ListOfThings)    '<<--  wrong field!!!   changed  "EventNumber" to "ListOfThings"
                    For lngCount = 0 To UBound(varSplit)
                        strSQL = "INSERT INTO tblThings (EventNumber, ListOfThings )"   '<<-- changed  "Thing" to "ListOfThings"
                        strSQL = strSQL & " VALUES (" & !EventNumber & ", '" & varSplit(lngCount) & "');"   '<<-- need to concatenate values
                        '    Debug.Print strSQL
                        CurrentDb.Execute strSQL, dbFailOnError
                    Next
                End If    '<<-- added
                .MoveNext
            Loop
        End With
    
        rst.Close
        Set rst = Nothing
        Set db = Nothing   '<<-- added
    
    End Sub

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    An alternative to VBA is Power Query/Get and Transform. You can import the table into PQ. You can then unpivot the column in Question. Save the file to excel and import it back into Access. Here is the MCode for PQ
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="TblOriginal"]}[Content],
        #"Split Column by Delimiter" = Table.SplitColumn(Source, "
    [ListOfThings]", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"
    [ListOfThings].1", "
    [ListOfThings].2", "
    [ListOfThings].3", "
    [ListOfThings].4"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"
    [ListOfThings].1", type text}, {"
    [ListOfThings].2", type text}, {"
    [ListOfThings].3", type text}, {"
    [ListOfThings].4", type text}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"[EventNumber]                  ", "[AnotherField] "}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
    in
        #"Removed Columns"

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

Similar Threads

  1. Splitting 2013 database
    By bigguy in forum Access
    Replies: 2
    Last Post: 04-29-2015, 04:12 PM
  2. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  3. Replies: 14
    Last Post: 03-01-2012, 02:39 PM
  4. Splitting a Database related questions
    By Matthieu in forum Access
    Replies: 5
    Last Post: 04-01-2010, 10:42 AM
  5. Splitting a Database related questions
    By Matthieu in forum Forms
    Replies: 3
    Last Post: 11-17-2009, 10:49 AM

Tags for this Thread

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