[dba-SQLServer] Cannot sort on Abs([Field]=0)

<quote>
The result of a comparison operator has the Boolean data type. This has three values: 
TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as 
Boolean expressions.

Unlike other SQL Server data types, a Boolean data type cannot be specified as the data 
type of a table column or variable, and cannot be returned in a result set.
</quote>

Note the last sentence.  IOW, there are very limited places in SQL Server queries that you 
can actually use "x = y" in an expression.

By using a VBA expression such as CBOOL() which is not valid SQL, you are forcing Access 
to process the query locally on raw data returned by SQL Server.  Without such an 
expression Access passes the actual comparison string to SQL Server resulting in the error.

 If you play around with "SortOrder = 1" in a query window in SSMS, you will find all sorts of 
similar problems.  

-- 
Stuart

On 16 Aug 2012 at 16:39, Gustav Brock wrote:

> Hi all
> 
> This works in Access SQL:
> 
>   ORDER BY Abs([SortOrder]=0)
> 
> but if the tables are linked via ODBC to SQL Server 2008, it fails:
> 
>   Incorrect syntax near '='. (#102)
> 
> I have to rewrite it like this:
> 
>   ORDER BY Abs(CBool([SortOrder]=0))
> 
> Why is that? [SortOrder] is a short integer with no Nulls.
> 
> /gustav
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>