Hi All,

I dug this thread out while trying to answer a question on another newsgroup. I get so tired of people who think they've found a bug in FoxPro when the bug is either in their code or in their understanding, so I stuck my neck out that it wasn't a bug, and then had to scramble to support my arugument. (Maybe that's how JimB learns things too?)

In this case I learned something that others might like to know.

KB Article says that with UDF's FoxPro actually goes through the indexing process in 3 passes, and that on the first pass it ignores any TRIM() functions. This poster had the strangest indexing routine, but without trimming it was attempting to index on a zero-length expression.

Here's some code to show what he was doing. I've expanded everything so that there is no longer a UDF.

CREATE CURSOR Dummy (Fy C(4), ;
	Fun1 C(10), Fun2 C(10), Fun3 C(10), ;
	NoTrim1 C(10), NoTrim2 C(10), NoTrim3 C(10))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	([1999], ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT(ALLTRIM([1999]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT(ALLTRIM([1999]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR(ALLTRIM([1999]), LEN(ALLTRIM([1999])), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT([1999], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT([1999], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR([1999], LEN([1999]), 1), 'FXRM0123456789'), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	([2000], ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT(ALLTRIM([2000]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT(ALLTRIM([2000]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR(ALLTRIM([2000]), LEN(ALLTRIM([2000])), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT([2000], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT([2000], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR([2000], LEN([2000]), 1), 'FXRM0123456789'), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	([99  ], ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT(ALLTRIM([99  ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT(ALLTRIM([99  ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR(ALLTRIM([99  ]), LEN(ALLTRIM([99  ])), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT([99  ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT([99  ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR([99  ], LEN([99  ]), 1), 'FXRM0123456789'), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	([6/9  ], ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT(ALLTRIM([6/9  ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT(ALLTRIM([6/9  ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR(ALLTRIM([6/9  ]), LEN(ALLTRIM([6/9  ])), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT([6/9  ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT([6/9  ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR([6/9  ], LEN([6/9  ]), 1), 'FXRM0123456789'), 1))
	
INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	([X   ], ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT(ALLTRIM([X   ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT(ALLTRIM([X   ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR(ALLTRIM([X   ]), LEN(ALLTRIM([X   ])), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT([X   ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT([X   ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR([X   ], LEN([X   ]), 1), 'FXRM0123456789'), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	([M   ], ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT(ALLTRIM([M  ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT(ALLTRIM([M   ]), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR(ALLTRIM([M   ]), LEN(ALLTRIM([M   ])), 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(RIGHT([M  ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(LEFT([M   ], 1), 'FXRM0123456789'), 1), ;
	SUBSTR('ADCBENMLKJIHGF', AT(SUBSTR([M   ], LEN([M   ]), 1), 'FXRM0123456789'), 1))

BROWSE

---

>Hi Charlie,
>
>Hmmmm....could be. I've never had occasion to use either in an index expression. Why would you want to?
>
>Jim always finds stuff like that :-)
>