Convert datatype Memo to datatype Text 1

[SebFr]

Hi,

I'm using Access 2003 and I'm having a query problem (again) with a linked table in SQL Server 2005.

Query:
SELECT Left([Comments],2) AS CSR, Sum(Orders.FinalGrandTotal) AS GrandTotal
FROM Orders
GROUP BY Left([Comments],2);

'Orders' is a linked table in SQL Server 2005, and 'Comments' is a field name of the table Orders.

When I run this query, I've the error message "ODBC - Call Failed". I've read that SQL Server doesn't like to use GROUP BY with the datatype Memo, and 'Comments' is defined as a Memo.
I can't change its type directly in the database so I want to convert it in the query by using the function Cstr()

New Query:
SELECT Left(Cstr([Comments]),2) AS CSR, Sum(Orders.FinalGrandTotal) AS GrandTotal
FROM Orders
GROUP BY Left(Cstr([Comments]),2);

But when I run this new query, I've another error message "Data type mismatch in criteria expression".

Can anyone please help me ?

Thanks for your answer

Seb

 

[PHV]

You may try this:
SELECT Left$([Comments],2) AS CSR, Sum(FinalGrandTotal) AS GrandTotal
FROM Orders
GROUP BY Left$([Comments],2);

Or this:
SELECT CStr(Left([Comments],2)) AS CSR, Sum(FinalGrandTotal) AS GrandTotal
FROM Orders
GROUP BY CStr(Left([Comments],2));

Another way is to ask your dba to create a view in SQL Server 2005.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SebFr]

Thank you PHV for your answer, it works now with the second solution you gave me !