Thanks Naomi!
using the derived table works (once I use ALLTRIM() around the cInvoiceNo in the PADL() clause).
>Instead on Sub-query use an INNER JOIN with derived table (it's going to be quicker),
>add your extra (calculated) field into the list of the fields and use ORDER BY 1.
>
>e.g.
>
>SELECT Sales.* ;
> FROM Sales INNER JOIN (SELECT TOP 2 iSaleid, PADL(cInvoiceNo, 11) FROM Sales ORDER BY 2 DESC) tempOrd
>on Sales.iSaleID = tempOrd.iSaleID
>
>would it work?
>
>>Hi,
>>
>>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,