Solved

Format Currency

Posted on 2011-09-15
10
284 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36545308
SumUSDEquiv: IIf([CurrencyID] = 1, Format([Amount],"Currency"), Format([Amount] * [ExchangeRate], "Currency"))
0
 

Author Comment

by:pdvsa
ID: 36545430
Patrick: for some reason, it is not formatted as Currency.  There are no commas and no USD currency.  What do you think now?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36545450
You can try being explicit:

SumUSDEquiv: Format([Amount] * IIf([CurrencyID] = 1, 1, [ExchangeRate]), "$#,##0.00")
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:pdvsa
ID: 36545519
darn...still no format.  Any other ideas?   screenshot of SumUSDEquiv
0
 

Author Comment

by:pdvsa
ID: 36545525
Also:  why isnt my image clearly visible? You have to click and dl it to view it I believe?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36545591
I think I know why.  Please switch from Design view to SQL view, and paste the SQL statement here.
0
 

Author Comment

by:pdvsa
ID: 36545877
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
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36546201
Try:

SELECT Sum(tblLetterOfCredit.Amount) AS SumOfAmount, tblBanks.BankName, Format(Sum(IIf([CurrencyID]=1, [Amount], [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;

Open in new window


If that doesn't work:

SELECT Sum(tblLetterOfCredit.Amount) AS SumOfAmount, tblBanks.BankName, Format(Sum(IIf([CurrencyID]=1, [Amount], [Amount] * [ExchangeRate])), "$#,##0.00") 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;

Open in new window

0
 

Author Comment

by:pdvsa
ID: 36546929
Patrick, i will give that a spin... Will have to tomorrow and revert back.   Thank you sir...
0
 

Author Closing Comment

by:pdvsa
ID: 36550939
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question