Sort separated numbers in a text field

[Futures_Bright]

[Solved]: Sort separated numbers in a text field

Hi all,

In my database, one of the key records is identified by it's "Clause Number" as it appears in the document. Common convention is to number these as in the title example (7.15.1.2) however the number of levels of numbering changes even within a document - only the separator '.' stays the same.

I currently have it set up to A-Z sort on the Clause Number (a text field to allow the format to be maintained, but also the odd occasion where a document uses titles rather than a numbering system). As well as for items such as titles rather than numbers, this approach is limited when the numbers in between the separators hit double digits.

To counteract this I have added a 'manual order' integer field to be used if necessary, and this order supercedes the order of the Clause Number in every section of my database. I'm now setting up a form to allow the user to re-order the items more easily where my initial thinking was 'number the "Manual Order" field in the same order as it is sorted to begin with and then let the user move records up/down as they please.

However it would be more user-friendly if I could create a button to read the format and order based on that (rather than a user click 10+ times to move each and every item falling under 4.2.xx above 4.10.xx-4.19.xx for example). However I'm not too sure how to do this with variable lengths (i.e. potentially unlimited separators).



Does anyone know how to set this up? The way I see it there needs to be two sections to this code:

1. Convert the numbers between separators into integer variables (perhaps a prepatory step of counting the number of variables required?)
2. Arrange the Clauses in order of left to right, once complete assign a number in the current order.

The main problem I foresee is the 'variable' number of variables being assigned.

Any help or alternative solutions are greatly appreciated!


Kind regards,

Michael

 

[Brianwarnock]

You might like to look at this as an option

http://www.access-programmers.co.uk/forums/showthread.php?t=120275

I don't think that the number of levels is an issue if each matching level is sorted properly from left to right priority.

Brian

 

[Futures_Bright]

Hi Brian,

Thanks for the link, it is certainly a much more elegant solution than the one I had in mind! I'm just having some troubles with the implementation - I've set up some code to assign the value of the fSortCode to a new field - "AutoSort Code" (which will be hidden from all but admins).

I'm now struggling to assign a counter (to the Manual Order field) in the order of the AutoSort Code; at the moment I'm trying to change the order displayed (using Me.OrderBy - which doesn't appear to work) and then running through the recordset with a counter. Can anyone help me polish this code off? Is there a way of doing this all through code without me needing the AutoSort Code as a field in the table?

 

[Brianwarnock]

I have to confess to not having used this, a poster I was helping found it and it resolved her problems with alpha numeric sorting in groups in reports, it looked like it should work for you but I am not experienced with it to help with a debug.

I do not really understand the bit about titles and a manual order code, why not just let the titles alpha sort?

Brian

 

[Futures_Bright]

Basically this will be fine for probably 97% of the cases within the table (these being documents from various sources that don't abide by the same conventions. Some only use Titles for each paragraph rather than a numbering system which means the user is required to sort manually). I need to allow the user to override this for the cases when it doesn't work.

So I don't need any help with the code in the link - that works fine - it is more how I use it to get the result that I want (which is basically to put an integer in the "Manual Order" field which corresponds to the order of the codes generated by fSortCode - this link - which I have put in a new field called "AutoSort Code")

 

[Brianwarnock]

I think I understand but I don't have an answer, I think that I would have to get to know the data , the result expected, and fsortcode's working better, I hope that you can work it through as I think it is very much a local issue.

Brian

 

[Futures_Bright]

I will do my best to explain a bit more detail in the hope that someone can help me out. I understand this particular problem is a bit niche, but it now relies on a combination of (what I presume to be at least) fairly common procedures.

The data in this table is based on the 'clauses' (numbered/titled paragraphs) of documents. The index for each paragraph ("Clause Number") is in the above mentioned format - e.g. 7.15.2.1 - for the vast majority of these documents, but not all (I can give examples of when but this is not important, the result is that I need to allow the user to number manually).

This is why I've created an integer field called "Manual Order", so that when A-Z ordering of the "Clause Number" doesn't work, there is a way that the user can set the order manually.

The reason these clauses need to be in order is to save time when reviewing updated documents. Likewise the purpose for this "AutoSort" Button as I've dubbed it on my form and in my code, is to save the user time when manually adjusting the order.

fSortCode as a function, works beautifully and all you really need to know from this point on is that the result will put the items in the common format in the correct order.

What I now have thanks to fSortCode is an extra field containing the alphanumeric code ("AutoSort Code") which will sort the vast majority of documents into the correct order. What I need is a way to tell VBA to assign an integer from 1 to n for all records in the current form in the order of "AutoSort Code". I don't know the best way to do this - I have a couple of ideas in mind for how to get around this - but I expect that the actual coding based on my logic will be very inefficient and there must be a better way (but at least will work... once I figure out how to write it down in VBA!).

 

[Futures_Bright]

Success! I've managed to get it working.

For anyone that might be interested, the code I used is below (as well as the fSortCode from the link above copied and pasted as-is). It is probably possible to code it more efficiently but it is much faster than a user doing it manually so I'm leaving it there!

Private Sub AutoSortButton_Click()
Dim rs As DAO.Recordset
Dim intTemp As Integer
 
intTemp = 1
On Error GoTo err_AutoSort
If MsgBox("Are you sure you want to AutoSort? This will overwrite any order currently in place.", vbCritical + vbYesNo, "AutoSort") = vbYes Then
    Set rs = Me.RecordsetClone
    rs.Bookmark = Me.Bookmark
 
    With rs
        .MoveFirst
        Do Until .EOF
            Me.AutoSort_Code = fSortCode([Clause Number])
            .MoveNext
            Me.Bookmark = .Bookmark
        Loop
    End With
 
    'requery to set in order - query order based on: Order Number > AutoSort Code > Clause Number
    Me.Refresh
    'assign integer to Order Number
    rs.MoveFirst
    Me.Bookmark = rs.Bookmark
 
    With rs
        .MoveFirst
        Do Until .EOF
            Me.Order_Number = intTemp
            intTemp = intTemp + 1
            .MoveNext
            Me.Bookmark = .Bookmark
        Loop
    End With
 
    'reset intTemp
    intTemp = 1
    rs.MoveFirst
    Me.Bookmark = rs.Bookmark
    'remove AutoSort Code from all entries
    With rs
        .MoveFirst
        Do Until .EOF
            If Me.Clause_Number <> "" Then
                Me.AutoSort_Code = ""
            Else
 
            End If
            .MoveNext
            Me.Bookmark = .Bookmark
        Loop
    End With
 
Else
End If
 
SortComplete:
Me.Requery
exit_AutoSort:
Exit Sub
err_AutoSort:
If Err.Number = 3021 Then
    Resume Next
Else
    If gcfHandleErrors Then
        Call PROC_ERR
        GoTo exit_AutoSort
    End If
End If
 
End Sub

It probably isn't necessary to remove the AutoSortCode after completion but I did so to ensure the Clause Number is the next level of order after the manual order (in case it is cleared).

 

[Brianwarnock]

Congratulations on your success , thank you for reporting back and sharing your code.

Brian