none
Split Funtion for a field that stores numeric values RRS feed

  • Question

  • I have used this split function in other databases in splitting an array but I can't seem to get it work with this database. The field that I wish to split stores the data in as numeric values but in the table, the names are presented. I have a multivalued field in SharePoint list that is linked in my access database and this list has multiple names in some of the fields. Below is the function that I am using:

        Dim dbs As DAO.Database
        Dim rst1 As DAO.Recordset
        Dim rst2 As DAO.Recordset
        Dim arr As Variant
        Dim i As Long
        
        Set dbs = CurrentDb
       
        Set rst1 = dbs.OpenRecordset("RICE Inventory", dbOpenForwardOnly)
        Set rst2 = dbs.OpenRecordset("Rice Inventory2", dbOpenDynaset)
        Do While Not rst1.EOF
            arr = Split(rst1.[Business Owner], ",")
            For i = 0 To UBound(arr)
                rst2.AddNew
                rst2!ID = rst1!ID
                rst2![Status] = arr(i)

                rst2.Update
            Next i
            rst1.MoveNext
        Loop
        rst2.Close
        Set rst2 = Nothing
        rst1.Close
        Set rst1 = Nothing
        Set dbs = Nothing
       
    End Sub

    The data looks like this:

    ID

    Business Owner

    1 Mohler, James   A, Kadar, Laura
    2 Mushill,   Kay L, Bowker, Michael J
    3 Kizer,   Jane S
    4 Debord,  Jason D, Lugge, Joshua D, Sugumar, Senthil Kumar, Moran, John J, London, Dan,   Tripathy, Kartikeya, Aziz, Mudassir M, Burns, James
    5 Rackley,   Eric C
    6 Steppig,   Harlan, Gada, Paul N, Janga, Madhusudhana R
    7 Ellebracht,   Michael
    8 Bayless,   Mike J
    9 Close,   Joshua
    10 Loken,   Samantha M
    11 Kirkpatrick,   Ray R
    12 Coimbra,   Kesley
    13 Kraemer,   Brian
    14 Mcdavid,   Kristine L

    ID

    Business Owner
    1 20732;20733
    2 20734;1513
    3 2811
    4 5764;270;6075;9199;7736;15804;17040;783
    5 404
    6 10819;20739;17642
    7 123
    8 83
    9 19805
    10 20749
    11 20751
    12 18693
    13 19011
    14 20756
    15 13190

    Data Type for these two fields are:

    ID                        AutoNumber

    Business Owner    Number

    I wanted to use a separate table to populate a form using a query that is joined on the number field to the number field of a table that has the actual names associated with the numbers and the actual stored values are separated by ";".  I think my main problem is the declaring of the arr and the "i" as an integer.  I get an arrow message with either type mismatch.

    Can someone help?


    Robert D. Lloyd

    Monday, April 3, 2017 8:53 PM

All replies

  • A few things:

    Split() will not work since the field in question is multi-valued (at least I think it is!).

    Also, if any name ever has a “,” in the text, then the split() would mess up.

    Dr, Smith, Albert Kallal

    Perhaps you data is really nice, but in above we have a “,” in place of “.” And thus the split() likely would mess up.

    Split() will not work on multi-value columns. However “behind” the scenes Access sees a multi-value column as in fact a SEPARATE table.

    So the approach is “open” the table to a recordset (you done that correctly). And then for EACH record, you open a another reocrdset to the ONE column that holds the multi-value data.

    You code will thus look “something” like:


         Dim rst1 As DAO.Recordset
         Dim rst2 As DAO.Recordset
         Dim rstMulti As DAO.Recordset2
        
         Dim arr As Variant
         Dim i As Long
        
         Set dbs = CurrentDb
        
         Set rst1 = dbs.OpenRecordset("RICE Inventory", dbOpenForwardOnly)
         Set rst2 = dbs.OpenRecordset("Rice Inventory2", dbOpenDynaset)
         Do While Not rst1.EOF
        
            Set rstMulti = rst1![Business Owner].Value
           
            Do While Not rstMulti.EOF
               rst2.AddNew
               rst2!ID = rst1!ID
               rst2![Status] = rstMulti(0)
               rst2.Update
               
               rst1.MoveNext
            Loop
            rstMulti.Close
         Loop
         rst2.Close
         Set rst2 = Nothing
         rst1.Close
         Set rst1 = Nothing
         Set dbs = Nothing
        
     End Sub

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Monday, April 3, 2017 10:26 PM
  • Thanks for the response Albert.  This seems to work for only one record.  When it hits the first "Loop" it goes back to the "Do While Not rstMulti.EOF" and then I get a message saying "Object Invalid or No longer set".  I am not an advance VBA developer so I don't quite know what to do to have it loop as well as have the object set each time. Your help is so appreciated!!!

    Robert D. Lloyd

    Tuesday, April 4, 2017 12:41 PM
  • Also, it is not working through each name for each record.  ID 1 has two names, ID 2 has two names, ID 4 has 4 names, etc. This may not work at all though because as you mentioned, there is a "," after the last name and a space for some names that have an initial and then a "," after the full name. I may have to think of another way. It is a strange table in SharePoint for even though the data type is a number and in the conversion displaying in the table through Access there is a name and even when splitting it throws the name in the Rice Inventory 2 table, the underlying data is a number that corresponds to the name. If I export the SharePoint List to Excel, the field shows

    Mushill, Kay L;#20734;#Bowker, Michael J;#1513 and so forth in the column for each record.  But in the form that is linked to the table, it shows a single number in the field with no "#" sign.  That is where I would like to see the multiple names instead of the number but it will not show more than one number.  In the drop down for the field, it shows a check box where I can check multiple names and they will display as a number but I didn't want to have to update the form manually nor do I want the numbers to display but rather the name.  I wanted to update the table linked to the form with the data from SharePoint and then when changes occur, the user can update through the form.  I know it sounds strange but that is what is needed.  Any suggestions?


    Robert D. Lloyd

    Tuesday, April 4, 2017 1:08 PM
  • Yes, that’s the issue with “air code”. There is a mistake on m part

    Try this:
    Do While Not rst1.EOF

       Set rstMulti = rst1![Business Owner].Value
            
       Do While Not rstMulti.EOF
          rst2.AddNew
          rst2!ID = rst1!ID
          rst2![Status] = rstMulti(0)
          rst2.Update
                
          rstMulti.MoveNext   <-- this changed
       Loop
       rstMulti.Close
    Loop

    So the fact that it worked for one record is a good sign. As I stated, multi-value columns are from a logical point of view seen as a WHOLE separate table in Access.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Tuesday, April 4, 2017 4:47 PM
  • Well we got it.  The last update worked but it kept looping through the same record...the first one...then I added the rst1.MoveNext after the rstMulti.Close and it worked like a charm.  Thank you again very much!!!

    Robert D. Lloyd

    Wednesday, April 5, 2017 1:18 PM
  • Good catch on your part. The missing the 2nd rst1.MoveNext was required, and of course I had rst1 in place of rstMulti.MoveNext

    The main lesson is how you can "dig into" those multi-value columns as per above - they are seen by Access as a separate table.

    Good luck!

    Regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, April 6, 2017 11:10 PM
  • Hi Rob Lloyd,

    from your last post , I can see that your issue is solved now.

    the thread is still open.

    please try to mark the suggestion given by Albert D. Kallal as an answer.

    so that we can close this thread.

    if you do not mark the answer then the thread will remain open.

    so I suggest you to mark the helpful post as an answer , it will also helps other community members in future.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 26, 2017 8:22 AM
    Moderator