Q:

Delphi SQL subselect error in MS-Access

I'm using Delphi 7 with ADO components, and MS Access 2003. The SQL sentence
SELECT CMCB.Name, 
     (SELECT SUM(amount) FROM movement MCB
     WHERE MCB.movement_classification_id=CMCB.movement_classification_id
     AND MCB.operation_date >= #01/01/2013#
     AND MCB.operation_date < #01/01/2014#
 ) AS MyYear 
 FROM movement_classification CMCB

is working fine in MS Access console but through a Delphi application launches the following error when I am opening the DataSet (TADOQuery):
Data provider or other service returned an E_FAIL status
Any idea why it happens? Is it related with the ADO component (TADOQuery in this case)
I tried a similar query from the database dbdemos.mdb (Program Files\Common Files\Borland Shared\Data) and it works
SELECT CustNo, 
    (SELECT SUM(AmountPaid) FROM orders O 
    WHERE O.CustNo = C.CustNo 
    AND O.SaleDate >= #01/01/1994# 
    AND O.SaleDate < #01/01/1995#
) AS AmountPaid 
FROM customer C

The code I used in Delphi is the following:
procedure TForm1.Button1Click(Sender: TObject);
begin
    ADOConnection1.Connected := False;
    ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Xiber\Delphi\StackOverflow\Subquerys\dbdemos.mdb';
    ADOConnection1.Connected := True;

    ADOQuery1.SQL.Text := 'SELECT CustNo, (SELECT SUM(AmountPaid) FROM orders O WHERE O.CustNo = C.CustNo AND O.SaleDate >= #01/01/1994# AND O.SaleDate < #01/01/1995#) AS AmountPaid FROM customer C';
    ADOQuery1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
    sSQL: string;
begin
    ADOConnection1.Connected := False;
    ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Xiber\Delphi\StackOverflow\Subquerys\XiGest-CASA.mdb';
    ADOConnection1.Connected := True;

    sSQL := ' SELECT CMCB.Name, ' +
        ' (SELECT SUM(amount) FROM movement MCB ' +
        ' WHERE MCB.movement_classification_id=CMCB.movement_classification_id ' +
        ' AND MCB.operation_date >= #01/01/2013# ' +
        ' AND MCB.operation_date < #01/01/2014# ' +
        ' ) AS MyYear ' +
        ' FROM movement_classification CMCB ';
    ADOQuery1.SQL.Text := sSQL;
    ADOQuery1.Open;
end;

A:

Finally I realised the difference between the two sums was that in dbdemos the field AmountPaid.mdb is double and in my case is decimal(8,2). 
It seems to be an ADO bug. You can reproduce by yourself.
So, If you change the field AmountPaid in dbdemos.mdb (provided by Borland, you can found at Program Files\Common Files\Borland Shared\Data) to decimal(8,2) and execute the query through Delphi 7 (with an ADOConnection and an ADOQuery), you'll get the error above mentioned.
SELECT CustNo, 
    (SELECT SUM(AmountPaid) FROM orders O 
    WHERE O.CustNo = C.CustNo 
    AND O.SaleDate >= #01/01/1994# 
    AND O.SaleDate < #01/01/1995#
) AS AmountPaid 
FROM customer C

But if you execute this query inside MS Access, it works fine.

