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

Consider using [regular expressions](https://stackoverflow.com/a/22542835/111794). Or, perhaps you could just use the VBA [`Split`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function) function, although I'm not sure you can pass in more than one character as the delimiter (and I [can't test it](https://superuser.com/questions/1409396/vba-unavailable-from-office-programs) right now). — Zev Spitz, Feb 26 '19 at 19:01
Don't put VBA variables within `[ ]`. Only Access object (field, table, query, form, report) names. Do you want to save the parts to separate records or as a single string? — June7, Feb 26 '19 at 20:01
Is the new table `Notes` a dependent table? What is the identifier that will associate these records? If you create 3 new records out of those parts, how will they retain association with each other? — June7, Feb 26 '19 at 20:12
I'm early in the stages, but I'll have an employee_ID field associated with each. Adding that to my append query should be easy, but it's parsing and splitting the text that's been throwing me off. I'll give the code below a try and hope for the best! Thanks! — tenebris silentio, Feb 27 '19 at 20:08
I tried the Split() method and it worked beautifully. THANK YOU! — tenebris silentio, Feb 27 '19 at 22:12

Tate Garringer · Accepted Answer · 2019-02-26T20:07:49.727

@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

June7 · Answer 2 · 2019-02-26T20:09:31.997

0

Assuming the string always starts with [] and preference is to return a single string, consider:

Replace(Mid(reporttext, 4), "[] ", vbCrLf)

edited Feb 26 '19 at 20:09

answered Feb 26 '19 at 20:02

June7

19,874
8
24
34

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

2 Answers2