If SET ANSI is off than VFP9 result is correct. In the query with SET ANSI OFF comparison is only performed to the lenght of the shortest operand regardless if it is on the left or the right of the '=' sign. IOW, ALLTRIM() of empty ln value will produce zero lenght string which will match any expression on the right.
The rules are different for comparison in non-sql statements and covered in help for SET EXACT command.
>I have an SQL command:
>
>SELECT IDNUM FROM CONTDB WHERE UPPER(ALLTRIM(ln)) = 'VOSS'
>
>In VFP8 22 recs returned in .78 secs
>In VFP9 243 recs return in 23.47 secs
>
>The extra records have a blank LN
>
>
>COUNT FOR UPPER(ALLTRIM(ln)) = 'VOSS' shows 22 in both 8&9
>
>I have an index on:
>
>UPPER(ALLTRIM(ln))
>
>Any idea?
--sb--