Sorry, you've lost me here. Could you try explaining what problem you are seeing again, please?


>Looks like you found some really weird bug.
>
>SELECT TOP 2 iSaleid FROM Sales ORDER BY PADL(cInvoiceNo, 11) DESC
>
>When I run your program, the c_test was magically transformed into just 2 records cursor. Even if I just create the cursor and then only run the last command. Even if I run it into the cursor nofilter.
>
>Something weird is going on with the TOP clause.
>
>
>>Hi Sergey,
>>
>>SET ANSI ON apparently does not affect the result. Here's the code I'm trying:
>>
>>

CREATE CURSOR c_Test;
>>(iSaleid i,;
>>cInvoiceNo c(11),;
>>cDesc C(10))
>>
>>FOR i = 1 TO 11
>>	INSERT INTO c_Test (iSaleid, cInvoiceNo, cDesc) VALUES (i, TRANSFORM(i), TRANSFORM(i))
>>ENDFOR
>>
>>SET ANSI ON
>>
>>SELECT c_Test.*;
>>	FROM c_Test ;
>>	WHERE iSaleid in (SELECT TOP 2 isaleid FROM c_Test ORDER BY cInvoiceNo desc)
>>
>>SET ANSI OFF
>>
>>SELECT c_Test.*;
>>	FROM c_Test ;
>>	WHERE iSaleid in (SELECT TOP 2 isaleid FROM c_Test ORDER BY cInvoiceNo desc)

>>
>>my result has 8 and 9, not 10 and 11.
>>
>>
>>
>>>Check String comparison in VFP
>>>

>>>SET ANSI ON
>>>SELECT Sales.* ;
>>>   FROM Sales ;
>>>   WHERE iSaleID IN (SELECT TOP 2 iSaleid FROM Sales ORDER BY cInvoiceNo DESC)
>>>

>>>
>>>>
>>>>I have a query like this:
>>>>
>>>>

SELECT Sales.* ;
>>>>   FROM Sales ;
>>>>   WHERE iSaleID IN (SELECT TOP 2 iSaleid FROM Sales ORDER BY cInvoiceNo DESC)

>>>>
>>>>The problem I'm having is due to the fact that if I have invoices entered with cInvoiceNo from "1" to "11", I want Invoices "10" and "11" to be returned, but I'm getting Invoices "1" and "11". So I really want to do something like this:
>>>>
>>>>

SELECT Sales.* ;
>>>>   FROM Sales ;
>>>>   WHERE iSaleID IN (SELECT TOP 2 iSaleid FROM Sales ORDER BY PADL(cInvoiceNo, 11) DESC)

>>>>
>>>>but of course that gives me a syntax error.
>>>>
>>>>Any ideas what I can do? I have no control over the invoice numbers as they are being returned from QuickBooks.
>>>>
>>>>Thanks,