Microsoft KB Archive/90412

{| = How to Create an Index in Descending Order =
 * width="100%"|

ID: Q90412

The information in this article applies to:


 * Microsoft FoxBASE+ for MS-DOS, version 2.10
 * Microsoft FoxPro for MS-DOS, version 1.02

SUMMARY
By default, indexes are created in ascending order (for example, alphabetically from A to Z) or by date from January to December. However, in some cases, there is a need to create an index in reverse (descending order such as alphabetically from Z to A) or by date from December to January. This article shows you how to create an index in descending order.

Numeric Fields
To create a reverse index on a numeric field, create a value that is the difference between an arbitrary large value and the value being indexed. For example, if SALES is a numeric field in a database and MyIndex is an indexname, the following creates a reverse index on sales:

INDEX ON 10000000 - SALES TO MyIndex

Date Fields
The same principle applies to create a reverse index on a date field. If lastsale is a field in the database and MyIndex is an indexname, the following code creates a reverse index on the date field:

INDEX ON CTOD(&quot;01/01/2100&quot;) - lastsale TO MyIndex

Character Fields
The procedure to create a reverse index on a character field requires three steps:

1. Create the following program called MCOMP.PRG to create a 255

character string, as follows:

PRIVATE char, mdescend char = 255 mdescend = &quot;&quot; DO WHILE char >= 1 mdescend = mdescend + CHR(char) char = char - 1 ENDDO RETURN mdescend 2. Assign the string generated by this program to a variable, as  follows:

mdesc_ord = mcomp

Note: This variable must be created each time the descending index is used. Otherwise, the program returns a &quot;Variable Not Found&quot; error. 3. Create the index using the SYS(15) function, the variable, and the database field.

For example, if the mdesc_ord variable is set as indicated in Step 2 above, lname is a field in the database and MyIdex is the indexname, the following code creates a reverse index on the lname field:

INDEX ON SYS(15,mdesc_ord,lname) TO MyIndex

Note: The SYS(15,, and uses the character's numeric value as a   subscript into the table , replacing the character in    with the character found at that position in the table.

Index on Two Fields [Field1 Ascending, Field2 Descending]
To create an index on two fields where the first field is indexed in ascending order and the second field is indexed in descending order, refer to the character field example above. The same concept could apply to more than two fields. Follow Steps 1 and 2 above, and then Step 3 below:

3. Create the index using the first field, the SYS(15), the variable,

and the second field.

For example, if the mdesc_ord variable is set as indicated in Step 2 above, fname is the first field to be indexed in ascending order, lname is the second field to be indexed in descending (reverse) order, and MyIndex is the indexname, the following code creates the index:

INDEX ON fname+SYS(15,mdesc_ord,lname) TO MyIndex Additional reference words: FoxDos 2.00 2.10 reverse KBCategory: kbusage KBSubcategory:
 * }