Need to seperate text from text box 1

[miaka13]

I have a textbox with various words in it that are separated by a comma. I still pretty new when is comes to VBA coding and need some help. What I am trying to do is to separate each string and place the words onto a separate record on a table. For example, the user enters:

a,b,c,d

I want the information to look like this on the table:

a
b
c
d

Can anyone help me with this?

 

[psemianonymous]

Two parts:
1. The VBA Split function will cleanly slice your string into four chunks. If you're using Access 2000 or newer, Split is provided for you. (for Access97 and below, we can find a Split function written for us by google-ing for one)

2. Use an SQL "INSERT INTO" statement to insert them into a table.


I can't give more specific help without more detail, but that should get you started.

 

[LambertH]

Are you using Access 2000 or later? If so take a look at the Split() function. It will break your string up at the commans for you.

 

[miaka13]

Thanks guys. This sounds like it could work for me. Could one of you give me an example of how to write a split function? I'm still pretty new at coding. Thanks in advance

 

[psemianonymous]

Google is my friend:

'found this at:
'[URL unfurl="true"]http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=OMCG6rgQCHA.1676%40tkmsftngp12&rnum=8[/URL]

Public Function Split( _
            Expression As String, _
            Delimiter As String, _
            Optional ByVal Limit As Long = -1, _
            Optional ByVal Compare As Integer = 0) _
        As Variant
    '-----------------------------------------------------------
    ' Inputs: String to search,
    '         delimiter string,
    '         optional replacement limit (default = -1 .. ALL)
    '         optional string compare value (default vbBinaryCompare)
    ' Outputs: Array containing items found in the string
    '           based on the delimiter provided
    ' Original code by: John L. Viescas  5-Sep-2001
    ' Extensively revised by: Dirk Goldgar  21-Jan-2002
    ' Last Revision: Dirk Goldgar  21-Jan-2002
    ' ** Duplicates the functionality of the VB 6 SPLIT function.
    '-----------------------------------------------------------
    Dim lngCnt As Long
    Dim intIndex As Integer
    Dim lngPos As Long
    Dim lngI As Long
    Dim strArray() As String

    If (Compare < -1) Or (Compare > 2) Then
        Err.Raise 5
        Exit Function
    End If
    ' If count is zero, return an empty array
    If Limit = 0 Then
        Split = Array()
        Exit Function
    End If
    ' If the Delimiter is zero-length, return a 1-entry array
    If Len(Delimiter) = 0 Then
        ReDim strArray(0)
        strArray(0) = Expression
        Split = strArray
        Exit Function
    End If

    ' Start count at (Limit - 1) because function returns
    ' whatever is left at the end.
    lngCnt = Limit - 1
    ' Start scanning at the start of the string.
    lngPos = 1
    ' Loop until the counter is zero.
    Do Until lngCnt = 0
        lngI = InStr(lngPos, Expression, Delimiter, Compare)
        ' If the delimiter was not found, end the loop.
        If lngI = 0 Then Exit Do
        ' Add 1 to the number returned.
        intIndex = intIndex + 1
        ' Expand the array to fit in a new element.
        ReDim Preserve strArray(0 To intIndex - 1)
        ' Use index - 1 .. zero-based array
        strArray(intIndex - 1) = Mid$(Expression, lngPos, lngI - lngPos)
        ' Advance past the found entry and the delimiter.
        lngPos = lngI + Len(Delimiter)
        lngCnt = lngCnt - 1
    Loop
    ' Everything after the last delimiter found goes in the last entry of
    ' the array.
    intIndex = intIndex + 1
    ReDim Preserve strArray(0 To intIndex - 1)
    If lngPos <= Len(Expression) Then
        strArray(intIndex - 1) = Mid$(Expression, lngPos)
    Else
        strArray(intIndex - 1) = vbNullString
    End If

    ' Return the result
    Split = strArray

End Function

 

[miaka13]

Thanks Foolie12! This will help me lots! Having an example in front of me is very helpful in helping me to write my own code. Thanks again for your help!