CODEWITHSUNDEEP
exceldatabasems-accesslogic
Arved Friese
Arved Friese

Reputation: 35

Access Database sort text into columns

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. enter image description here

Upvotes: 0

Views: 64

Answers (1)

Applecore
Applecore

Reputation: 4099

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,

Upvotes: 1

Related Questions