ODBC problem with Access Queries to linked tables to SQL Server 2000

Giganews Newsgroups
Subject: ODBC problem with Access Queries to linked tables to SQL Server 2000
Posted by:  boris_a…@hotmail.com
Date: 16 Aug 2006

Help,

I have an Access database with link tables to a SQL Server 2000
database.

When I run the following query, it works OK:

  SELECT Format([Issue_Date],"mmmm yyyy") AS DateTXT,
          Sum([Current_State]='CANCELLED') AS Cancelled
  FROM ECNDetails
  GROUP BY Format([Issue_Date],"mmmm yyyy")

However, when I try to run this one, it fails:

  SELECT Issue_Date AS DateTXT,
          Sum([Current_State]='CANCELLED') AS Cancelled
  FROM ECNDetails
  GROUP BY Issue_Date

It gives me two messages:

"ODBC -- call Failed"
and
"[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '='. (#170)"

I have checked the DSN for the linked tables. I have re-created all the
linked tables. Both password and authorities are correct.

So, can anybody tell me why the first query would run but the second
wouldn't.

Any help is very much appreciated.

Thanks.

Replies