currency conversion of calculated field

[Ali29J]

Hi all

I m looking to make calculations based on the currency, so that all totals can be returned in US$, at present i can only list as separate totals, for each of the 3 currency.

I have the fololwing in a query

Currency - Groupby
calculatedtotal: PO!POUnitPrice*PO!QtyOrdered

is it possible to enter calculation to say IF - Currency=EUR, then times the calculated result by form text box with the entered conversion rate, ELSEIF currency =GBP then ....etc

To establish all totals in US$???

Appreciate any pointers

Thanks

Ali

 

[PHV]

You may consider the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ali29J]

Hi PHV

managed to work it, i have then put these resuls into another query so i can add them together, but not working quite right...

SELECT DISTINCTROW Format$([SalesPersonOrdersTW].[Date],'mmmm yyyy') AS [Date By Month], Sum(SalesPersonOrdersTW.calculatedtotal1) AS [Sum Of calculatedtotal1], Sum(SalesPersonOrdersTW.calculatedtotal2) AS [Sum Of calculatedtotal2], Sum(SalesPersonOrdersTW.calculatedtotal) AS [Sum Of calculatedtotal], ([ Sum Of calculatedtotal2] + [Sum Of calculatedtotal1] + [Sum Of calculatedtotal]) AS [Sum USD]

The last one i simply want all the previous added together to get a grand total, but it keeps displaying "enter parameter value "Sum of calculatedtotal2", how can i simply ad the 3 sum of calculated together?? what am i doin wrong...

Thanks

Ali

 

[PHV]

..., (Sum(SalesPersonOrdersTW.calculatedtotal1) + Sum(SalesPersonOrdersTW.calculatedtotal2) + Sum(SalesPersonOrdersTW.calculatedtotal)) AS [Sum USD]
...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ali29J]

Hi PHV

Sorry me again, i ve tried the following but it does not return any results under Sum USD, just blanks against each month

SELECT DISTINCTROW Format$([SalesPersonOrdersTW].[Date],'mmmm yyyy') AS [Date By Month], ((Sum(SalesPersonOrdersTW.calculatedtotal1))+(Sum(SalesPersonOrdersTW.calculatedtotal2))+(Sum(SalesPersonOrdersTW.calculatedtotal))) AS [Sum USD]

ANy ideas...tried alsorts

 

[PHV]

And this ?
..., (Sum(Nz(SalesPersonOrdersTW.calculatedtotal1,0)) + Sum(Nz(SalesPersonOrdersTW.calculatedtotal2,0)) + Sum(Nz(SalesPersonOrdersTW.calculatedtotal,0))) AS [Sum USD]
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ali29J]

Hi PHV worked a treat, thanks for your patients