How to Index a Character Field That Contains Digits

Last reviewed: June 27, 1995
Article ID: Q126620
The information in this article applies to:
  • Microsoft FoxPro for Windows, version 2.6a
  • Microsoft FoxPro for MS-DOS, version 2.6a
  • Microsoft FoxPro for Macintosh, version 2.6a

SUMMARY

A table may contain a character field in which the entries are right aligned (right justified) and contain only digits or digits and a non- alphabetic character. To index this field correctly, you must use a combination of functions as detailed in this article.

MORE INFORMATION

An example of such a field is a table of contents field. For example, a table could contain two fields: a table of contents field and a memo field. The memo field is the text itself, and the table of contents field represents the positioning of that text within a document. The following example assumes the Table of Contents field is named TOC and is five characters in size. If the records are entered in random order, you may have five records in which the TOC field has the following values:

   Record 1  "    2"
   Record 2  "  1.1"
   Record 3  "  2.1"
   Record 4  "    1"
   Record 5  "1.1.1"

You can create an index on the TOC field by typing this:

   INDEX ON toc TAG toc

However in the example, this places Record 1 before Record 2. To correctly index a field that has a combination of alphanumerics and non-alphanumerics use this command:

   INDEX ON PADR(ALLTRIM(toc),LEN(toc)) TAG mtoc

Using this command, the Index order for the sample data above will start with Record 4, followed by Record 2 and so on.


Additional reference words: FoxWin FoxDos FoxMac 2.60a
KBCategory: kbprg
KBSubcategory: FxprgTable


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 27, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.