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

Of course people connecting to a MS SQL or real SQL server would never do
the computing in the MS Access FE...would they??

Jim 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Thursday, August 16, 2012 3:39 PM
To: Discussion concerning MS SQL Server
Subject: Re: [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
> 
> 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com