Solved

Format Currency

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

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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