SQL-COMPUTE

[SAMS]

Hi all, Have this small question about COMPUTE. As per below is the code that i used but it appears a syntax error(missing operator).

The code: ( EXPORT_ACC is my table)

Adodc1.recodsource = "Select VENDOR, GP, PEE From EXPORT_ACC WHERE VENDOR = 'NEWCASTL' COMPUTE(SUM(TOTALBILL)) BY Dept"

Adodc1.Refresh
.
.
.

I had double check all my fields, but the error this appears.

Pls give me some advices . Thks. If you got better command pls show me . Thks again.

[JHausmann]

You'll need an "order by" if you want to "compute BY", as in:

Adodc1.recodsource = "Select VENDOR, GP, PEE From EXPORT_ACC WHERE VENDOR = 'NEWCASTL' order by Dept COMPUTE(SUM(TOTALBILL)) BY Dept"

[SAMS]

Thks. I actually added the "order by dept" but the system still give me the syntax error in query expression "Dept COMPUTE(SUM(TOTALBILL)) BY Dept""

Furthermore I had checked all my fields are correct.Is there a miss out somewhere??
Hope someone can help.
Thks.

[JHausmann]

I hate to ask the obvious question but is your query all on one line?

[Aaron Young]

The problem is there are no Parenthesis "()" in the COMPUTE Statement, try this:

Adodc1.Recordsource = "SELECT VENDOR,GP,PEE FROM EXPORT_ACC WHERE VENDOR='NEWCASTL' ORDER BY Dept COMPUTE SUM(TOTALBILL) BY Dept"

------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

[SAMS]

Thks guys, but the methods doesn't seen to work. Yes, the command is all in one line.
does anyone have any other ways??

Pls help. Thks a lot.

[Aaron Young]

Are you still getting the Error? Or can't you find your SubTotals? If you can't fnd your Subtotals, they are returned in a seperate Resultset.

------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

[SAMS]

Yes, I'm still getting the same error message. Any other ways to detect the problem?