Solved

format Currency, revmove decimals

Posted on 2013-12-05
12
358 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

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)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

12 Experts available now in Live!

Get 1:1 Help Now