Microsoft KB Archive/247650

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

Article ID: 247650

Article Last Modified on 6/23/2005

-

APPLIES TO


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



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.



MORE INFORMATION
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:  Create a new Access database.  Create a new table with the following specifications:   Table: MyNumbers Field Name: ID  Data Type: AutoNumber

Field Name: TestNum Data Type: Number   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                      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  On the File menu, click Save . Click Yes in the Save dialog box.</li> In the Save As dialog box, you can leave the module name as is, and then click OK.</li> On the File menu click Close and Return to Microsoft Access.</li> In the Database window, click Queries, and then click New. Click Design View, and then click OK.</li> Add the MyNumbers table to the query design grid, and then close the Show Table dialog box.</li>  Create a new query with the following characteristics: <pre class="fixed_text">  Query Name: TestSort --  Field: TestNum Sort: Leave Blank

Field: CommonNum: OrderString(TestNum) Sort: Ascending </li>  On the Query menu, click Run. Note that you receive the following results: <pre class="fixed_text">  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                    </li></ol>

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.