Board index » delphi » ADOQuery and SELECT SUM

ADOQuery and SELECT SUM

Hi

In Access 2000 I have a successful query -

SELECT STOCKPRICES.Day, Sum(STOCKPRICES.Close) AS SumOfClose
FROM STOCKPRICES
WHERE (((STOCKPRICES.StockId)=40))
GROUP BY STOCKPRICES.Day;

When I set this as the value of the SQL in TADOQuery I get an unspecified
error...

Any ideas? I had a quick browse through the newsgroups and could find things
touching on this but not directly related.

Thanks
Paul

 

Re:ADOQuery and SELECT SUM


FYI

I was using the Jet 4.0 Ole DB Provider for the connection and when I
changed it to the OLE DB Provider for ODBC then it worked...

Does JET not support aggregates in SQL then?

Cheers
Paul

Quote
"Paul Cleverley" <p...@vic.co.uk> wrote in message news:3d5e8640$1@dnews...
> Hi

> In Access 2000 I have a successful query -

> SELECT STOCKPRICES.Day, Sum(STOCKPRICES.Close) AS SumOfClose
> FROM STOCKPRICES
> WHERE (((STOCKPRICES.StockId)=40))
> GROUP BY STOCKPRICES.Day;

> When I set this as the value of the SQL in TADOQuery I get an unspecified
> error...

> Any ideas? I had a quick browse through the newsgroups and could find
things
> touching on this but not directly related.

> Thanks
> Paul

Re:ADOQuery and SELECT SUM


Quote
>I was using the Jet 4.0 Ole DB Provider for the connection and when I
>changed it to the OLE DB Provider for ODBC then it worked...

>Does JET not support aggregates in SQL then?

Jet support aggregates but CLOSE is an SQL key word

try

SELECT STOCKPRICES.Day,
Sum(STOCKPRICES.[Close]) AS SumOfClose
FROM STOCKPRICES
WHERE (((STOCKPRICES.StockId)=40))
GROUP BY STOCKPRICES.Day;
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:ADOQuery and SELECT SUM


The aggregate is fine, but you are using JET reserved words as column names
e.g. day and close.
See
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q248738&id=24...
for a full list.

And you are advised to use OLEDB provider rather than ODBC.

Dave

Quote
"Paul Cleverley" <p...@vic.co.uk> wrote in message news:3d5e937d@dnews...
> FYI

> I was using the Jet 4.0 Ole DB Provider for the connection and when I
> changed it to the OLE DB Provider for ODBC then it worked...

> Does JET not support aggregates in SQL then?

> Cheers
> Paul

Re:ADOQuery and SELECT SUM


Quote
>The aggregate is fine, but you are using JET reserved words as column names
>e.g. day and close.
>See
>http://support.microsoft.com/default.aspx?scid=kb;en-us;Q248738&id=24...
>for a full list.

That list actually contains quite a few words that are not JET SQL key words.
DAY is one of them.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:ADOQuery and SELECT SUM


Doh - when will I ever learn...?

Thanks guys

Other Threads