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