SQL in VB Data Class Module

SQL in VB Data Class Module

Post by Judge » Fri, 24 Aug 2001 00:18:13



I have the below SQL Statement in MS Access 2000 which
works a Treat :

SELECT TestQuestions.Product, TestQuestions.Area,
TestQuestions.Grade, Difficulty.QuestionNo, Sum
(Difficulty.QUsage) AS QsUsage, Sum(Difficulty.TotalMP) AS
TotalPossible, Sum(Difficulty.TotalMA) AS TotalAchieved,
(100-([TotalAchieved]/[Totalpossible])*100) AS PDifficulty
FROM [SELECT OldTestAnswers.TestNumber,
OldTestAnswers.QuestionNo, Sum
(OldTestAnswers.MarkPossible) AS TotalMP, Sum
(OldTestAnswers.Mark) AS TotalMA, 1 AS QUsage FROM
OldTestAnswers GROUP BY OldTestAnswers.TestNumber,
OldTestAnswers.QuestionNo]. AS Difficulty INNER JOIN
TestQuestions ON Difficulty.QuestionNo =
TestQuestions.QuestionNo GROUP BY TestQuestions.Product,
TestQuestions.Area, TestQuestions.Grade,
Difficulty.QuestionNo HAVING (((TestQuestions.Product)
="Example Test 1"));

but As soon as I try to use it in VB with an ADODB
Recordset using the statemnt AREC.Open "...  it comes back
with the error :

-2147217887
You tried to execute a query that does not include the
specified expression '100-[TotalAchieved]/[Totalpossible]
*100' as part of an aggregate function.

I don't understand why it works in Access 2000 but not in
in VB 6 ?

Anybody, please can you help ?