How to Index an Address Field (133453)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft FoxPro for Windows 2.5
  • Microsoft FoxPro for Windows 2.5a
  • Microsoft FoxPro for Windows 2.5b
  • Microsoft FoxPro for Windows 2.6
  • Microsoft FoxPro for Windows 2.6a
  • Microsoft FoxPro for MS-DOS 2.5
  • Microsoft FoxPro for MS-DOS 2.5a
  • Microsoft FoxPro for MS-DOS 2.5b
  • Microsoft FoxPro for MS-DOS 2.6
  • Microsoft FoxPro for MS-DOS 2.6a
  • Microsoft FoxPro for Macintosh 2.5b
  • Microsoft FoxPro for Macintosh 2.5c
  • Microsoft FoxPro for Macintosh 2.6a
This article was previously published under Q133453

SUMMARY

Indexing on an address field with "Index on address to myidx" does not list the streets in alphabetical order if the street name is in the middle of the address field (i.e. 111 Main Street).

MORE INFORMATION

The function addsort is passed a string which is converted and returns a string in a new form i.e. 123 Main Street -----> MAINSTREET00000123

  1. On the FoxPro File menu, click New. Then select Program as the file type, and click New.
  2. Type or paste the following program into the Untitled.prg window. 
         FUNCTION addsort
   PARAMETER xxx
   xxx=ALLTRIM(UPPER(xxx))
   wordnum=OCCURS(' ',xxx)+1  && A space between the quotation marks
   DIMENSION mychar(wordnum)
   mychar=''                  && No spaces between the quotation marks
   yyy=''                     && No spaces between the quotation marks
   x_begin=1
   IF LEN(xxx)>0
   *  The following FOR/NEXT breaks the address down into separate words.
   FOR i=1 TO wordnum
      x_end=AT(' ',xxx,i)-x_begin && A space between the quotation marks
      IF x_end<1                  && No spaces in the string
        x_end=LEN(xxx)
      ENDIF
      mychar(i)=SUBSTR(xxx,x_begin,x_end) && single word in address field
      x_begin=x_begin+x_end+1
      IF x_begin>LEN(xxx)
        i=wordnum
      ENDIF
   ENDFOR
   * The following FOR/ENDFOR  grabs all the character words and
   * build a new string. (1st, 2nd, 3rd, 4th, etc. are considered
   * character words.
   FOR i=1 TO wordnum
      IF ISALPHA(mychar(i))
         yyy=yyy+mychar(i)
      ENDIF
      IF ISDIGIT(mychar(i))
         IF 'ST'$mychar(i) .OR. 'ND'$mychar(i) .OR. 'RD'$mychar(i) .OR. ;
            'TH'$mychar(i)
            yyy=yyy+LEFT('00000000',10-LEN(mychar(i)))+mychar(i)
            mychar(i)=''  && No spaces between the quotation marks
         ENDIF
      ENDIF
   ENDFOR
   * The following FOR/ENDFOR grabs all the numeric words and appends
   * it to the end of the new string.
   FOR i=1 TO wordnum
      IF ISDIGIT(mychar(i)) .AND. .NOT. ;
         (INLIST(mychar(i),'ST','ND','RD','TH'))
         yyy=yyy+LEFT('00000000',8-LEN(mychar(i)))+mychar(i)
      ENDIF
   ENDFOR

   ELSE
      Y=" "   && A space between the quotation marks
   ENDIF
   RETURN(yyy)
  3. On the FoxPro File menu, click Close. Click Yes when prompted to save changes to untitled.prg. Then type "addsort.prg" without the quotation marks when prompted to "Save As." Then click Save on the "Save As" window.
  4. On the FoxPro Window menu, click View and then Open. Select a table to be indexed, and choose Open.
  5. Assume the database field to be indexed is called Address. In the Command window type: 
          INDEX ON addsort(address) TO myidx
Modification Type:MajorLast Reviewed:12/3/2003
Keywords:kbcode KB133453
©2003 Microsoft Corporation. All rights reserved.