Hi,

I encountered an error while attempted to run a query in
a Excel-VBA subroutine to get data in a MDB database.
error reported as following:

Run time error'-2147217887'
You tried to execute a query that does not include the
specified expression 'Period' as part of an aggregate
function.

But I do have the field 'period', would somebody can help?

John,
What's the SQL behind the query ?

NickHK

"JohnDing" wrote in message
...
Hi,

I encountered an error while attempted to run a query in
a Excel-VBA subroutine to get data in a MDB database.
error reported as following:

Run time error'-2147217887'
You tried to execute a query that does not include the
specified expression 'Period' as part of an aggregate
function.

But I do have the field 'period', would somebody can help?

SQL = "SELECT Period, CDF_No, SAFE_No, Invoice_No,
Currency, Sum(Total_Amount) AS Total_Amount_Sum, Country
FROM EXPSALESDB GROUP BY Invoice_No HAVING ((Period ='" &
Period & "')) ORDER BY Invoice_No"

Thanks, Nick
-----Original Message-----
John,
What's the SQL behind the query ?

NickHK

"JohnDing" wrote in message
...
Hi,

I encountered an error while attempted to run a query
in
a Excel-VBA subroutine to get data in a MDB database.
error reported as following:

Run time error'-2147217887'
You tried to execute a query that does not include the
specified expression 'Period' as part of an aggregate
function.

But I do have the field 'period', would somebody can
help?


.

"JohnDing" wrote ...

SQL = "SELECT Period, CDF_No, SAFE_No, Invoice_No,
Currency, Sum(Total_Amount) AS Total_Amount_Sum, Country
FROM EXPSALESDB GROUP BY Invoice_No HAVING ((Period ='" &
Period & "')) ORDER BY Invoice_No"

You would need to add to your GROUP BY cluse the columns in you SELECT
clause that are not being used in a set function (e.g. SUM) i.e. add
Period, CDF_No, SAFE_No, [Currency] to the GROUP BY clause. BTW I
think you need Currency in brackets because it is a reserved word in
Jet 4.0 (and is not very useful so consider renaming).

Jamie.

--