Solved

format Currency, revmove decimals

Posted on 2013-12-05
12
361 Views
Last Modified: 2013-12-07
Experts,

I am trying to remove the decimals and format as currency.  Is below correct?   thanks

USD Equivalent: Format(IIf([tblCurrencyExchange].[CurrencyID]=1,"",Format(([tblFXRates]![Amount]*[tblCurrencyExchange]![ExchangeRate]),"Currency","#")))
0
Comment
Question by:pdvsa
  • 7
  • 5
12 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 350 total points
ID: 39698476
Try this ...

USD Equivalent: Format(Round(IIf([tblCurrencyExchange].[CurrencyID]=1,"",[tblFXRates]![Amount]*[tblCurrencyExchange]![ExchangeRate]), 0),"$#,##0")



ET
0
 

Author Closing Comment

by:pdvsa
ID: 39698563
OK thanks.
0
 

Author Comment

by:pdvsa
ID: 39698565
OK thanks.

It is a little odd that I get a #error on only one particular record.  Would you know why?  
error on one record
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:pdvsa
ID: 39698573
the [Amount] and [tblCurrencyExchange]![ExchangeRate] are populated...little odd would get that error.  
recopied here:
USD Equivalent: Format(Round(IIf([tblCurrencyExchange].[CurrencyID]=1,"",[tblFXRates]![Amount]*[tblCurrencyExchange]![ExchangeRate]), 0),"$#,##0")
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39698614
For the #Error record what are the contents in the following fields ...

[CurrencyID]
[Amount]
[ExchangeRate]


ET
0
 

Author Comment

by:pdvsa
ID: 39698748
Let me know if you see something....screen print below showing those columns

#error
0
 

Author Comment

by:pdvsa
ID: 39698751
it is the only row with a currencyID = 1
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39698836
Ok, the USD Eauivalent calculated formula will return "" for the value which cannot be formatted to currency ... Try this instead.

USD Equivalent: Format(Round(IIf([tblCurrencyExchange].[CurrencyID]=1,0,Nz([tblFXRates]![Amount], 0)*Nz([tblCurrencyExchange]![ExchangeRate], 0), 0),"$#,##0")

ET
0
 

Author Comment

by:pdvsa
ID: 39699427
ahh I see.  

It gives me a wrong number of args.  I have played around with parenth but couldnt figure it out.   It hightlights the 2nd parent.

]![ExchangeRate], 0), 0),"$#,##0")
                                              ^
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39699583
Yes, you are missing a closing parenth ... Try this one ...

USD Equivalent: Format(Round(IIf([tblCurrencyExchange].[CurrencyID]=1,0,Nz([tblFXRates]![Amount],0)*Nz([tblCurrencyExchange]![ExchangeRate],0)),0),"$#,##0")


ET
0
 

Author Comment

by:pdvsa
ID: 39703638
thanks for the help.  That worked perfectly.  

have a good one....
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39703805
Ok, glad to help and thanks for the points.  

ET
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

773 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