MS Access capture certain group of text, append, and loop onto next section in a long text field

Question

I have a long text field (called "reporttext") that someone is importing a bunch of text that needs to be separated and appended into another table. For each case, there's a "[]" character that is supposed to separate each case. I want my code to look for the first [] and second [], append the text to another table and then loop. So the next case would be the text between the second [] and third [].

Here's my string

Reporttext: [] ksfjjls [] 42244 [] @@@@

I would want this to append to a new table called "notes" where it would be like this:

Reporttext
ksfjjls
42244
@@@@

I used a macro to count the number of [] in the text file to know how many times to run the loop, but this, along with the rest of my code just isn't happening. I know my code is wrong, but I know with a few tweaks it'll get there. Any help is appreciated.

lengthofnote = Len([reporttext])
start = InStr([reporttext], "[]")
startplus3 = [start] + 3
'find number of cases
firstcase = 1
numcases = StringCountOccurrences([reporttext], "[]")
Dim LCounter As Integer

  For LCounter = [firstcase] To [numcases]
    revisedreporttext = Mid([reporttext], [startplus3], [lengthofnote])
    secondposition = InStr([revisedreporttext], "[]")
    nextreporttext = Mid([reporttext], [startplus3], [secondposition])
    Add_reporttext = "INSERT INTO notes(reporttext) values ('" & nextreporttext & "');"
    DoCmd.RunSQL Add_reporttext  
    firstcase = firstcase + 1
    startplus3 = secondposition
    secondposition = secondposition + 4
  Next LCounter

Answer 1

@Zev Spitz is correct in that you could use Split() to accomplish this. You could use something like this

Option Compare Database
Option Explicit
Sub SplitLongTextField()
    Dim rs As Recordset
    Dim reportTextArr
    Dim qString As String
    Dim i As Long


    qString = "SELECT [reporttext] FROM [Table1]" '<- replace [Table1] with the name of your table with the Long Text field

    Set rs = CurrentDb.OpenRecordset(qString)

    If Not rs.EOF Then
        reportTextArr = Split(rs.Fields("reporttext"), "[]")
    End If

    For i = LBound(reportTextArr) To UBound(reportTextArr)
        If Not reportTextArr(i) = "" Then
            DoCmd.RunSQL "INSERT INTO notes(reporttext) VALUES('" & reportTextArr(i) & "');"
        End If
    Next i

    rs.Close

End Sub

If you needed to do this for multiple records from your initial table then you could loop through the entire table and loop the operation like

Option Compare Database
Option Explicit
Sub SplitLongTextField()
    Dim rs As Recordset
    Dim reportTextArr
    Dim qString As String
    Dim i As Long


    qString = "SELECT [reporttext] FROM [Table1]" '<- replace [Table1] with the name of your table with the Long Text field

    Set rs = CurrentDb.OpenRecordset(qString)

    Do Until rs.EOF
        reportTextArr = Split(rs.Fields("reporttext"), "[]")

        For i = LBound(reportTextArr) To UBound(reportTextArr)
            If Not reportTextArr(i) = "" Then
                DoCmd.RunSQL "INSERT INTO notes(reporttext) VALUES('" & reportTextArr(i) & "');"
            End If
        Next i

        rs.MoveNext
    Loop

    rs.Close

End Sub