Hi Peter,

Using ALLTRIM() in the query conditions is asking for trouble. Try to run you query w/o ALLTRIM() or change ALLTRIM() to PADR().

>Have a situation where SQL Select statement works perfectly every time in VFP8 but seems to fail when run in VFP9 under exact same scenario and environment. This has been reported to the official MSDN VFP Bug site. Any ideas or feedback anyone may have to offer here in UT would be most welcome. This is perplexing and dissappointing. Hope we have done something wrong here.
>
>The data sources used here are pre-updated and refreshed remote view cursors, via ODBC, of Enterprise SQL Server 2K data tables where SQL server is running on a QUAD XEON W2K Server platform. VFP is running on a desktop worktation which is a 3.2GHz P4 800FSB with 1G 300 MHz DDR RAM running XP Prof with SP1
>
>The basics here are that we are looking specifically to find any records, and only those particular records, that may be in 1 table that are not found in table 2 but which should be in table 2. Note below: "ChkDate" is table 1 which might hold some records - they will also be flagged with a 'True' bit in a "ChkDate" column called 'notused' - that should be in table 2 named "NotUsed" but which are not found in table 2. Pretty simple huh? Works in VFP8: Problem is; In VFP9, this code finds records that are concurrently in BOTH tables. If the data is already in table 2 as well as being in table 1, it should not report those records; it should report only records it may find in 1 that are NOT already in 2. Both 'short' and 'account' columns in these cursors populated with conventional alphanumeric strings no longer than 10 characters each - no nulls.

>Simplified code used is:
>* set defaults for gc_Short, gc_fwhen
>PUBLIC gc_Short, gc_fwhen
>gc_Short = 'exampleA'
>gc_fwhen = DTOS(DATE())
>
>SELECT ;
>    chkdate.short, ;
>    chkdate.account, ;
>    chkdate.billdate, ;
>    .. etc .., ;
>    chkdate.foundwhen, ;
>    'Transferring a "notused" Record From chkdate: ' + DTOC(DATE()) AS comments ;
>FROM ;
>    chkdate ;
>WHERE ;
>    chkdate.short = gc_Short AND ;
>    DTOS(chkdate.foundwhen) >= gc_fwhen AND ;
>    chkdate.notused AND ;
>    (ALLTRIM(chkdate.short) + ALLTRIM(chkdate.account)) NOT IN ;
>           (SELECT (ALLTRIM(notused.short) + ALLTRIM(notused.account)) ;
>            FROM notused ;
>            WHERE notused.short = gc_Short )

>Any ideas, anyone? /psb