pdvsa
asked on
Aggregate Function error
Experts, I am trying to add the below to a query and after doing so I get an error: "YOu tried to execute a query that does not include the specified expression 'Amount' as part of an aggregate function.
AccrueUSDEquiv: Sum(IIf([CurrencyID]=1,Nz( [TAccrueMo INLocalCur r],0),Nz(( [TAccrueMo INLocalCur r]*[Exchan geRate]),0 )))
Why do I get this error? The query works fine until I try to add this.
AccrueUSDEquiv: Sum(IIf([CurrencyID]=1,Nz(
Why do I get this error? The query works fine until I try to add this.
in order to use SUM, you must use an aggregate function on the rest of the columns such as GROUP BY
optionally, if you don't want to do that, you can use DSUM which needs the fieldname (or expression), table or query name, and conditions for matching
optionally, if you don't want to do that, you can use DSUM which needs the fieldname (or expression), table or query name, and conditions for matching
ASKER
Hi Crystal,
thank you. I didnt know I could do that. I would like to use the Dsum method.
I simply changed Sum to Dsum and i get a wrong number of arguments error.
I am sure there is more to it than simply doing what i did.
what should I do now? grateful for your help.
thank you. I didnt know I could do that. I would like to use the Dsum method.
I simply changed Sum to Dsum and i get a wrong number of arguments error.
I am sure there is more to it than simply doing what i did.
what should I do now? grateful for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. I will work on crafting something. Step by step.
Have a good night
Have a good night
you're welcome ~ happy to help
ASKER
its big
SELECT
tblDraws_Details1.Amount, tblDraws_Details1.ValueDat
FROM
tblDraws_Details1 INNER JOIN ((tblRepayment LEFT JOIN tblInterest_Sibor ON tblRepayment.ID = tblInterest_Sibor.RpmtID) LEFT JOIN tblCurrencyExchange ON tblRepayment.Currency = tblCurrencyExchange.Curren
WHERE
(((Format([tblRepayment].[
ORDER BY
tblDraws_Details1.ID;