Results 1 to 10 of 10
  1. #1
    hbtousa is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    8

    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. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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. #3
    hbtousa is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    8
    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.

    Thanks for your time

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this code would appear to do what you want

    Code:
    Function sortString(s) 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", 2 ' adsmallint
            
            .CursorLocation = 3 'adUseClient
            .LockType = 3 'adLockOptimistic
            .cursortype = 3 'adOpenStatic
    
            .Open
           
            For i = 1 To Len(s)
                If Mid(s, i, 1) Like "[A-Z]" Then
                    .AddNew
                    !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. #5
    hbtousa is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    8
    Thanks very much. I will give it a try.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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



    modify your code to this
    Code:
    Function sortString(s) As String
    Dim rs As Object
    Dim i As Integer
    Dim done As Boolean
    
    
        
        Set rs = CreateObject("ADODB.Recordset")
        With rs
            .Fields.Append "txt", 200, 2 ' advarchar
            .Fields.Append "num", 200, 3 ' advarchar
            
            .CursorLocation = 3 'adUseClient
            .LockType = 3 'adLockOptimistic
            .cursortype = 3 'adOpenStatic
    
    
            .Open
           
            For i = 1 To Len(s)
                If Mid(s, i, 1) Like "[A-Z]" Then
                    .AddNew
                    !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. #7
    hbtousa is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    8
    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 Click image for larger version. 

Name:	DataBase.PNG 
Views:	12 
Size:	18.2 KB 
ID:	39347Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	11.5 KB 
ID:	39348
    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. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    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,"<",""),">",""),"*","")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    hbtousa is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    8
    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.
    Click image for larger version. 

Name:	ThanksVeryMUch.PNG 
Views:	10 
Size:	43.5 KB 
ID:	39363

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 11-03-2018, 06:27 AM
  2. Replies: 7
    Last Post: 09-15-2018, 02:01 PM
  3. Replies: 6
    Last Post: 04-04-2017, 11:09 AM
  4. Sort on a field in a report
    By gehrenfeld in forum Reports
    Replies: 3
    Last Post: 07-20-2015, 11:15 AM
  5. Sort according to field length?
    By wawinc in forum Queries
    Replies: 4
    Last Post: 12-15-2010, 04:27 PM

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