[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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","#")))
0
pdvsa
Asked:
pdvsa
  • 7
  • 5
1 Solution
 
Eric ShermanAccountant/DeveloperCommented:
Try this ...

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



ET
0
 
pdvsaAuthor Commented:
OK thanks.
0
 
pdvsaAuthor 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
pdvsaAuthor 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
 
pdvsaAuthor Commented:
Let me know if you see something....screen print below showing those columns

#error
0
 
pdvsaAuthor 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
 
pdvsaAuthor 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
 
pdvsaAuthor 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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now