format Currency, revmove decimals

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","#")))
pdvsaProject financeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
Try this ...

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



ET
0
 
pdvsaProject financeAuthor Commented:
OK thanks.
0
 
pdvsaProject financeAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
pdvsaProject financeAuthor Commented:
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
 
Eric ShermanAccountant/DeveloperCommented:
For the #Error record what are the contents in the following fields ...

[CurrencyID]
[Amount]
[ExchangeRate]


ET
0
 
pdvsaProject financeAuthor Commented:
Let me know if you see something....screen print below showing those columns

#error
0
 
pdvsaProject financeAuthor Commented:
it is the only row with a currencyID = 1
0
 
Eric ShermanAccountant/DeveloperCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
pdvsaProject financeAuthor Commented:
thanks for the help.  That worked perfectly.  

have a good one....
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, glad to help and thanks for the points.  

ET
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.