asked on

Format Currency

Experts: I cant get this right. I need Currency format but get a data type mismatch. I assume I have something wrong in the below.

SumUSDEquiv: IIf([CurrencyID]=1,Format([Amount],"Currency",Format([Amount]*[ExchangeRate]),"Currency"))

Thank you

Patrick Matthews

SumUSDEquiv: IIf([CurrencyID] = 1, Format([Amount],"Currency"), Format([Amount] * [ExchangeRate], "Currency"))

pdvsa

ASKER

Patrick: for some reason, it is not formatted as Currency. There are no commas and no USD currency. What do you think now?

Patrick Matthews

You can try being explicit:

SumUSDEquiv: Format([Amount] * IIf([CurrencyID] = 1, 1, [ExchangeRate]), "$#,##0.00")

pdvsa

ASKER

darn...still no format. Any other ideas?

pdvsa

ASKER

Also: why isnt my image clearly visible? You have to click and dl it to view it I believe?

Patrick Matthews

I think I know why. Please switch from Design view to SQL view, and paste the SQL statement here.

pdvsa

ASKER

WEll I tried to copy over
Sum(IIf([CurrencyID]=1,Format([Amount],"Currency"),Format([Amount]*[ExchangeRate],"Currency"))) AS SumUSDEquiv

wtih Format([Amount] * IIf([CurrencyID] = 1, 1, [ExchangeRate]), "$#,##0.00") as SumUSDEquiv

but darn it gives me an error.

here is the SQL if that helps:

SELECT Sum(tblLetterOfCredit.Amount) AS SumOfAmount, tblBanks.BankName, Sum(IIf([CurrencyID]=1,Format([Amount],"Currency"),Format([Amount]*[ExchangeRate],"Currency"))) AS SumUSDEquiv
FROM tblCurrencyExchange RIGHT JOIN (tblLetterOfCredit INNER JOIN tblBanks ON tblLetterOfCredit.IssuingBankUSA = tblBanks.BankID) ON tblCurrencyExchange.CurrencyID = tblLetterOfCredit.Currency

WHERE (((tblLetterOfCredit.LCType)<>1 And (tblLetterOfCredit.LCType)<>11 And (tblLetterOfCredit.LCType)<>12 And (tblLetterOfCredit.LCType)<>14 And (tblLetterOfCredit.LCType)<>18 And (tblLetterOfCredit.LCType)<>21 And (tblLetterOfCredit.LCType)<>27 And (tblLetterOfCredit.LCType)<>30) AND ((tblLetterOfCredit.ExpiredYN)=0) AND ((tblLetterOfCredit.DateOfIssueSB) Is Not Null))

GROUP BY tblBanks.BankName;

untitled.PNG

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Patrick, i will give that a spin... Will have to tomorrow and revert back. Thank you sir...

pdvsa

ASKER

the first one worked!

I see that the format wraps all of it.

Sum(IIf([CurrencyID]=1,Format([Amount],"Currency"),Format([Amount]*[ExchangeRate],"Currency"))) AS SumUSDEquiv

to

Format(Sum(IIf([CurrencyID]=1, [Amount], [Amount] * [ExchangeRate])), "Currency") AS SumUSDEquiv

thank you very much.