Can I use 'exists' in the ORDER BY clause?

[markmarko]

I'd like to have an EXISTS clause in and ORDER BY Clause, but I keep getting
a syntax error, even though it looks clean.

Here it is:
ORDER BY Exists (SELECT [Record-Details-Sales].[ID] FROM
[Record-Details-Sales] WHERE [Record-Details-Sales].[SalesOrder] =
[Record-Orders-Sales].[SalesOrderID]) ;

 

[Douglas J. Steele]

I'm having a hard time imagining what that's supposed to do. ORDER BY tells
the sequence in which records are to be displayed, and has nothing to do
with whether or not a particular record exists. What are you trying to
accomplish?

 

[Vsn]

You might..... mean you like to have a query which checks if the record
exists in an other query, than sort it?

If that is the case, Use IN() instead of EXISTS......

SELECT tblBankNumbers.fID, Val([fBankNumber]) AS Num
FROM tblBankNumbers
WHERE (((tblBankNumbers.fID) In (SELECT tblBankNumbers.fID FROM
tblBankNumbers WHERE (((tblBankNumbers.fBankNumber) Like "*[8]*")) WITH
OWNERACCESS OPTION)
AND ((Val([fBankNumber]))>80))
ORDER BY Val([fBankNumber])
WITH OWNERACCESS OPTION;


Ludovic

 

[markmarko]

Ok, the sql is to grab a cluster of SalesOrders in order to export them to
our old system of job tracking while we transition to using the database
entirely. In that system, we sort jobs in various ways, one of which is
whether the order contains a particular Sales Code.

Therefore, I need to sort orders that have this particular Sales Code above
jobs that do not. The Sales Codes are in tblSalesDetails.

So to sort it, I need the ORDER BY clause. And what I'm intending to sort by
is whether or not a given order has a particular Sales Code in it's records
in tblSalesDetails.

Make sense?

 

[Douglas J. Steele]

ORDER BY IIf(IsNull(tblSalesDetails.[SalesCode]), 0, 1), Field1

where Field1 is whatever field you really want to sort on.