Solved

Format Currency

Posted on 2011-09-15
10
272 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

785 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