# Microsoft KB Archive/247650

(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

APPLIES TO

• Microsoft Access 2000 Standard Edition

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

## SUMMARY

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.
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