Solved

Format Currency

Posted on 2011-09-15
10
269 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run Time Error 3075 15 45
MS SQL - Rotating Values in SQL 9 53
Access coding 2 13
Access database form in matrix view 14 14
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now