Access Database sort text into columns

Question

I have a text, for example:

fp(638,17:57,hh,ab).
fp(638,19:47,ms,an).
fp(638,19:49,ms,ab).

I want to sort each part in the bracket, separated by the commas, in an Access column. Is there a easy way to do this. The result should look like the image pinnend to this question.

Thanks for your efforts.

With a formula, you could extrat the target string. Something like =MID(A1;4;15) should work. Then Text to columns may be useful too. — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Mar 16, 2021 at 7:41
Thanks for your answer! I would really appreciate it, if you could give me more in depth information about your answer. I’m fairly new to this. Greetings :) — Arved Friese
– Arved Friese, Commented Mar 16, 2021 at 7:51

Applecore · Accepted Answer · 2021-03-16 09:53:57Z

Assuming that the data is already is Access in a table in one column, you can use a few string manipulation functions to split the data into columns in a different table. Something like:

Sub sSplitData()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsIn As DAO.Recordset
    Dim rsOut As DAO.Recordset
    Dim strData As String
    Dim astrData() As String
    Set db = CurrentDb
    Set rsIn = db.OpenRecordset("SELECT DataIn FROM tblDataIn;")
    If Not (rsIn.BOF And rsIn.EOF) Then
        Set rsOut = db.OpenRecordset("SELECT * FROM tblDataOut WHERE 1=2;")    '    open a recordset that has no records selected for speed
        Do
            strData = rsIn!DataIn
            strData = Mid(strData, 4)   ' get rid of the "fp(" at the start
            strData = Left(strData, Len(strData) - 2)   '   get rid of the ")." at the end
            astrData = Split(strData, ",")  '   split the data based on commas
            With rsOut
                .AddNew
                ![Zug Nr] = astrData(0)
                !Zeit = astrData(1)
                !Stadt = astrData(2)
                ![AB/AN] = astrData(3)
                .Update
            End With
            rsIn.MoveNext
        Loop Until rsIn.EOF
    End If
sExit:
    On Error Resume Next
    rsIn.Close
    rsOut.Close
    Set rsIn = Nothing
    Set rsOut = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sSplitData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Regards,

Collectives™ on Stack Overflow

Access Database sort text into columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related