How to Create an Index in Descending Order

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.

MORE INFORMATION

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("01/01/2100") - 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 = ""
      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 "Variable Not Found"
   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,<expC1>,<expC2) function takes each character of
   the string <expC2> and uses the character's numeric value as a
   subscript into the table <expC1>, replacing the character in
   <expC2> 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:

Last Reviewed: April 17, 1995