Solved Move comma separated data in a field to rows (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 11:49
Joined
Jun 26, 2007
Messages
851
Hello, I have a field (DelimitedEmployees) from my table (tbl_WeeklySafetyHuddle) that are names/data saved as a comma delimited from a list box. Is there a function anyone knows of that will take that field in the table and separate the names into a temp table or something like that? I did find the code below for separating movie Genres but I dont really know what the line below is for so I can alter it for my needs?

Dim GenreList() As String

Code:
Public Sub GetGenres()
 
Dim rs As DAO.Recordset
Dim rs_out As DAO.Recordset
Dim x As Long
Dim GenreList() As String
 
    Set rs = CurrentDb.OpenRecordset("SELECT Films.Genre FROM Films")
    Set rs_out = CurrentDb.OpenRecordset("Genres_Temp")
    With rs
        Do
            GenreList = Split(!Genre, ", ")
            For x = LBound(GenreList) To UBound(GenreList)
                rs_out.AddNew
                rs_out!Genre = GenreList(x)
                rs_out.Update
            Next x
            .MoveNext
        Loop Until .EOF
    End With
    
    rs_out.Close
    Set rs_out = Nothing
    rs.Close
    Set rs = Nothing
    
    MsgBox "Done"
    
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:49
Joined
Sep 21, 2011
Messages
14,058
The Genre would be your DelimitedEmployees
 

oxicottin

Learning by pecking away....
Local time
Today, 11:49
Joined
Jun 26, 2007
Messages
851
I did that but all its doing is moving the same comma separated data to my temp tables field.

Its saving in temp table like:
joe1, joe2, joe3.....

I need it to be like:
Joe1
Joe2
Joe3

Code:
Public Sub GetGenres()

Dim rs As DAO.Recordset
Dim rs_out As DAO.Recordset
Dim x As Long
Dim GenreList() As String

    Set rs = CurrentDb.OpenRecordset("SELECT tbl_WeeklySafetyHuddle.DelimitedEmployees FROM tbl_WeeklySafetyHuddle")
    Set rs_out = CurrentDb.OpenRecordset("temp_ConvertDelimited")
    With rs
        Do
            GenreList = Split(!DelimitedEmployees, ", ")
            For x = LBound(GenreList) To UBound(GenreList)
                rs_out.AddNew
                rs_out!EmpConvertDelimited = GenreList(x)
                rs_out.Update
            Next x
            .MoveNext
        Loop Until .EOF
    End With
   
    rs_out.Close
    Set rs_out = Nothing
    rs.Close
    Set rs = Nothing
   
    MsgBox "Done"
   
End Sub
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:49
Joined
Sep 21, 2011
Messages
14,058
Walk through your code.
( I am going to have that on my gravestone :( )
Is there a space after the comma in the field?
Walking through your code would tell you what you have each time.
 

cheekybuddha

AWF VIP
Local time
Today, 15:49
Joined
Jul 21, 2014
Messages
2,238
Perhaps safer to:
Code:
' ...
            GenreList = Split(!DelimitedEmployees, ",")         ' Split on the comma only
            For x = LBound(GenreList) To UBound(GenreList)
                rs_out.AddNew
                rs_out!EmpConvertDelimited = Trim(GenreList(x)) ' Trim any preceding/trailing spaces
                rs_out.Update
            Next x
            .MoveNext
' ...
 

oxicottin

Learning by pecking away....
Local time
Today, 11:49
Joined
Jun 26, 2007
Messages
851
Perhaps safer to:
Code:
' ...
            GenreList = Split(!DelimitedEmployees, ",")         ' Split on the comma only
            For x = LBound(GenreList) To UBound(GenreList)
                rs_out.AddNew
                rs_out!EmpConvertDelimited = Trim(GenreList(x)) ' Trim any preceding/trailing spaces
                rs_out.Update
            Next x
            .MoveNext
' ...
Trim will take any spaces away? If so then yes thanks!
 

Users who are viewing this thread

Top Bottom