Bubble Sort Within The Same Field

1. Novice
Windows 7 64bit Access 2013 64bit
Join Date
Aug 2019
Posts
6

Bubble Sort Within The Same Field

Hello. I am trying to sort every line of a table; the table has 1000+records with data as follows:
LA60RH90RW23LE9LS10RE12RA140LH2RS5RK780LW2LK7
Every line has TWO letters and THREE "digits" twelves times.

I would like to sort every line in ascending order. For example the end result would be LW2LH2RS5LK7LE9LS10RE12RW23LA60RH90RA140RK780 .
I tried the bubble sort using a different programming language and it worked.However, I need to use Access since the amount of data is pretty big. Any help would be highly appreciate it, and just for the record I am a novice programmer and I don't how or where to start. Thanks a lot
The function I used and worked is described below:
Code:
```void sort(int Numbers[], int size) {
for (int First = 0; First < (size - 1); First++) {
for (int Inside = 0; Inside < (size - (First + 1)); Inside++) {
if (Numbers[Inside] > Numbers[Inside + 1]) {
int Stored = Numbers[Inside];
char *Temp = Parts[Inside];
Parts[Inside] = Parts[Inside + 1];
Numbers[Inside] = Numbers[Inside + 1];
Numbers[Inside + 1] = Stored;
Parts[Inside + 1] = Temp;
}```

2. VIP
Windows 10 Access 2010 32bit
Join Date
Mar 2015
Posts
8,380
so you are changing this
LA60RH90RW23LE9LS10RE12RA140
LH2RS5RK780LW2LK7
to this
LW2LH2RS5LK7LE9LS10RE12RW23LA60RH90RA140RK780

what is the logic in simple English?

Does this
Every line has TWO letters and THREE "digits" twelves times.
actually mean
Every line has TWO letters and UP TO THREE "digits"

You say you want to sort line in ascending order - how does the result you have represent ascending order? LW2 comes after LH2
so do you mean ascending order of the digits as numbers - And text does not matter and can be random?

Edit: one other question - is the objective to have a bubble sort algorithm, or to just get the string sorted? i.e. would a simpler, non bubble sort method be OK?

3. Novice
Windows 7 64bit Access 2013 64bit
Join Date
Aug 2019
Posts
6
Only the numeric fields should be sorted. The letters will follow the digits new position. Ex: If i have the months of the year and the number of the month next to ite, my table right now would look like AUG08JAN01DEC12FEB02... After sorting the end result would be JAN01FEB02MAR03....
The letters are always the same, they are not random. They represent the initials of 12 body parts: int Points[12] = {LShoulder, RShoulder, LElbow, ....};
I don't need a bubble sort algorithm, I just need the string sorted.
I mentioned "3 digits" because I probably need after sorting to convert the numeric values to text in order to have same size string for all the records.

4. VIP
Windows 10 Access 2010 32bit
Join Date
Mar 2015
Posts
8,380
this code would appear to do what you want

Code:
```Function sortString(s) As String
Dim rs As Object
Dim i As Integer

With rs
.Fields.Append "txt", 200, 2 ' advarchar

.Open

For i = 1 To Len(s)
If Mid(s, i, 1) Like "[A-Z]" Then
!txt = Mid(s, i, 2)
i = i + 2
!num = Val(Mid(s, i))
i = i + Len("" & !num) - 1
.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```
which in the immediate window

?sortstring("LA60RH90RW23LE9LS10RE12RA140LH2RS5RK7 80LW2LK7")
LH2LW2RS5LK7LE9LS10RE12RW23LA60RH90RA140RK780

5. Novice
Windows 7 64bit Access 2013 64bit
Join Date
Aug 2019
Posts
6
Thanks very much. I will give it a try.

6. VIP
Windows 10 Access 2010 32bit
Join Date
Mar 2015
Posts
8,380
I mentioned "3 digits" because I probably need after sorting to convert the numeric values to text in order to have same size string for all the records.
to make numbers consistently 3 chars
?sortstring("LA60RH90RW23LE9LS10RE12RA140LH2RS5RK7 80LW2LK7")
LH002LW002RS005LK007LE009LS010RE012RW023LA060RH090 RA140RK780

Code:
```Function sortString(s) As String
Dim rs As Object
Dim i As Integer
Dim done As Boolean

With rs
.Fields.Append "txt", 200, 2 ' advarchar
.Fields.Append "num", 200, 3 ' advarchar

.Open

For i = 1 To Len(s)
If Mid(s, i, 1) Like "[A-Z]" Then
!txt = Mid(s, i, 2)
i = i + 2
!num = Format(Val(Mid(s, i)), "000")
i = i + Len("" & Val(Mid(s, i))) - 1
.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```

7. Novice
Windows 7 64bit Access 2013 64bit
Join Date
Aug 2019
Posts
6
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

With rs
.Fields.Append "txt", 200, 2 ' advarchar
.Fields.Append "num", 200, 3 ' adsmallint
.Open

For i = 1 To 73
If Mid(fullbody, i, 1) Like "[A-Z]" Then
!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

8. VIP
Windows 10 Access 2010 32bit
Join Date
Mar 2015
Posts
8,380
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.

shame you didn't say that before, particularly after I clarified what you wanted - means I've wasted my time providing a solution that was not required. Regret I don't have time to redo it. Suggest you look at what the code does then modify it carefully or remove those additional characters. Or perhaps someone here or the other forum where you have crossposted can help.

9. Can't just call this function from VBA procedure like that. This function returns a value so it must be returned to something, such as a variable or Debug.Print or a textbox.

Debug.Print sortString(fullbody)

x = sortString(fullbody)

Me.tbxSort = sortString(fullbody)

But calling from query as Ajax suggests maybe best or from a textbox ControlSource.

I tested function with the extraneous characters in string and it works regardless.

However, can modify code to remove the extraneous characters. Replace() function can accomplish that -
Code:
`Replace(Replace(Replace(fullbody,"<",""),">",""),"*","")`

10. Novice
Windows 7 64bit Access 2013 64bit
Join Date
Aug 2019
Posts
6
Solved. My problem was not the data and/or the help provided. I didn't know that I had to make a new query to call and to show the function created by Ajax . Sorry about all the misunderstandings. Thanks for all the help provided.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums