I wrote the code in my DB, however I have an error:
Code:
Option Compare DatabaseDim db As Database
Dim rs As Recordset
Dim record As String
Private Sub Detail_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset("SerialKPoints", dbOpenDynaset, dbSeeChanges)
Do Until rs.EOF
fullbody = rs.Fields("SerialData")
sortString (fullbody)
rs.MoveNext
Loop
End Sub
Function sortString(fullbody) As String
Dim rs As Object
Dim i As Integer
Set rs = CreateObject("ADODB.Recordset")
With rs
.Fields.Append "txt", 200, 2 ' advarchar
.Fields.Append "num", 200, 3 ' adsmallint
.CursorLocation = 3 'adUseClient
.LockType = 3 'adLockOptimistic
.cursortype = 3 'adOpenStatic
.Open
For i = 1 To 73
If Mid(fullbody, i, 1) Like "[A-Z]" Then
.AddNew
!txt = Mid(fullbody, i, 2)
!num = Mid(fullbody, (i + 2), 3)
i = i + 5
.Update
End If
Next i
.Sort = "num ASC"
.MoveFirst
While Not .EOF
sortString = sortString & !txt & !num
.MoveNext
Wend
End With
Set rs = Nothing
End Function
On the first part of the code, I read all the records one by one and then I tried to run your code. Not sure what I did wrong
My data has at the beginning and at the end the signs <> to separate each line, as well as the * to separate each body part value. I can get rid of them if it needs to. I also converted the numbers to strings. I will continue digging into this. Please let me know if I have any coding error that triggers the debug errors. Thanks for your great help