query not running when I leave the AVG part out

[colmkav]

Can anyone see why the following query does not run? If I put AVG function around the "IIf([Created transactions (number)]<>0,[Created transactions (value)]*[Conversion rate]/[Created transactions (number)],0)" part it runs ok (but not the results I want obviously)

 

 

SELECT [MIND Accounts].[Client-id], [MIND Accounts].[Client-name], Sum(Data.[Created transactions (number)]) AS [SumOfCreated transactions (number)], Sum(Data.[Created refunds (number)]) AS [SumOfCreated refunds (number)], Sum([data].[Created transactions (value)]*[Conversion rate]) AS [Created transactions (EUR value)], Data.[Processing date], IIf([Created transactions (number)]<>0,[Created transactions (value)]*[Conversion rate]/[Created transactions (number)],0) AS [Avg trx amount]
FROM ([MIND Accounts] INNER JOIN Data ON [MIND Accounts].[Account-id] = Data.[Merchant CID]) INNER JOIN [Currency conversion] ON Data.Currency = [Currency conversion].Currency
GROUP BY [MIND Accounts].[Client-id], [MIND Accounts].[Client-name], Data.[Processing date]
HAVING (((Data.[Processing date])>=DateAdd("d",-13,[@ContextDate]) And (Data.[Processing date])<=[@ContextDate]));

[Lalit_Mohan]

Hi colmkav,

Please upload the database file so that we can check the same and help you.

Regards,

Lalit Mohan