Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010

    Unanswered: Splitting text using VBA

    I have a question about the data from a linked table from a SharePoint page. One of the fields in SharePoint is a "Choice" field where employees can place a check mark next to all options that apply. When i view this data in Access, when there are multiple selections made, it is put into this format...";#Stairstep HBO;#Stairstep Cinemax;#Stairstep Starz;#" . There is a semicolon and a hash tag before each selection. Now for my question, Is there any way to separate these selections and place them into individual columns in an access table when using a Make Table Query?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    You can use the Split() function to create an array with all the elements of a string:
    Sub SplitAndInsert()
        Dim str As String
        Dim var As Variant
        Dim i As Long
        str = ";#Stairstep HBO;#Stairstep Cinemax;#Stairstep Starz"
        var = Split(str, ";#")
        For i = 0 To UBound(var)
            Debug.Print i, var(i)
        Next i
    End Sub
     1            Stairstep HBO
     2            Stairstep Cinemax
     3            Stairstep Starz
    You can then use the contents of the array to compose a query, although I cannot imagine why you would want to use a Make Table Query in such a case. I guess you have reasons I don't know anything about.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts