Microsoft KB Archive/247650

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:50, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base

ACC2000: How to Sort a Field of Numbers Based on All Permutations

Article ID: 247650

Article Last Modified on 6/23/2005


  • Microsoft Access 2000 Standard Edition

This article was previously published under Q247650

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).


In a query, you may want to sort a field based on all permutations of each group of digits. For instance, all of the following records would appear together in the sort:

5678, 6785, 7856, 8567, 7568, 8756

These records would appear together because they are all based on the digits 5678. What sequence the digits of each number are in is not relevant, as long as the matching numbers appear together in the sort. This article demonstrates a way to do this.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.


You can accomplish this task by defining an extra column in your query that contains a custom function that takes any number and sorts its digits from left to right. For example, the function takes 8675 and turns it into 5678. If the function is given 6758, the function turns this number into 5678 as well. You can call a number like 5678 the "common" number.

By sorting on the column of common numbers, you can get a list of numbers grouped together according to the individual digits that they contain. The following steps demonstrate how to do this:

  1. Create a new Access database.
  2. Create a new table with the following specifications:

       Table: MyNumbers
       Field Name: ID
       Data Type: AutoNumber
       Field Name: TestNum
       Data Type: Number
  3. Type the following data into the table:

       ID      TestNum
       ---     -------
       1       1111
       2       1122
       3       1212
       4       1234
       5       2569
       6       5269
       7       9652
       8       4956
       9       4587
       10      8745
       11      5678
       12      6785
       13      7568
       14      7856
       15      8567
       16      8756
       17      5689
       18      8899
       19      9898
       20      8999
       21      9998
  4. In the Database window, click Modules, click New, and then type the following code in the new module:

    Function OrderString(xString As String)
    Dim stringArray() As String
    Dim First As Integer, Last As Integer
    Dim i As Integer, j As Integer
    Dim Temp As String
    Dim returnString As String
    stringlen = Len(Format(xString, 0))
    ReDim stringArray(1 To stringlen)
    For i = 1 To stringlen
        stringArray(i) = Mid(xString, i, 1)
    Next i
        First = LBound(stringArray)
        Last = UBound(stringArray)
        For i = First To Last - 1
            For j = i + 1 To Last
                If stringArray(i) > stringArray(j) Then
                    Temp = stringArray(j)
                    stringArray(j) = stringArray(i)
                    stringArray(i) = Temp
                End If
            Next j
        Next i
    For i = 1 To stringlen
        returnString = returnString & stringArray(i)
    Next i
    OrderString = returnString
    End Function
  5. On the File menu, click Save <database name>. Click Yes in the Save dialog box.
  6. In the Save As dialog box, you can leave the module name as is, and then click OK.
  7. On the File menu click Close and Return to Microsoft Access.
  8. In the Database window, click Queries, and then click New. Click Design View, and then click OK.
  9. Add the MyNumbers table to the query design grid, and then close the Show Table dialog box.
  10. Create a new query with the following characteristics:

       Query Name: TestSort
       Field: TestNum
       Sort:  Leave Blank
       Field: CommonNum: OrderString(TestNum)
       Sort:  Ascending
  11. On the Query menu, click Run. Note that you receive the following results:

       TestNum   CommonNum
       -------   ---------
       1111      1111
       1122      1122
       1212      1122
       1234      1234
       2569      2569
       5269      2569
       9652      2569
       4956      4569
       4587      4578
       8745      4578
       5678      5678
       6785      5678
       7568      5678
       7856      5678
       8567      5678
       8756      5678
       5689      5689
       8899      8899
       9898      8899
       8999      8999
       9998      8999

NOTE: If you do not want to see the CommonNum column, you can return to Design view and click to clear the Show check box for that column.

Keywords: kbhowto KB247650