• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

Format as Currency

Experts, I seem to have an error of "too many arguments" in the below:

="Total for " & [cboPmtMadeYN] & " (USD Equiv):" & format(Sum([Rpmt USD Equiv],"Currency")

I need to format the [Rpmt USD Equiv] as currency.

thank you
0
pdvsa
Asked:
pdvsa
  • 6
  • 5
  • 3
  • +2
1 Solution
 
Shaun KlineLead Software EngineerCommented:
You are missing an end parenthesis for your Sum function.
format(Sum([Rpmt USD Equiv]),"Currency")

Open in new window

0
 
Shaun KlineLead Software EngineerCommented:
Depending on your version of MS Access you can also use the FormatCurrency function.
0
 
D PatelD Patel, Software EngineerCommented:
Try with this:

="Total for " & [cboPmtMadeYN] & " (USD Equiv):" & format(Sum([Rpmt USD Equiv]),"Currency")
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
D PatelD Patel, Software EngineerCommented:
Shaun Kline is right. You are missing parenthesis for Sum Function
0
 
pdvsaAuthor Commented:
Hi,

thank you.  I thought that currency format would change the text color to RED if negative but I dont believe it will.  

I modified to the below but it still doesnt change the text font to red for negative.
="Total for " & [cboPmtMadeYN] & " (USD Equiv):  " & Format(Sum([Rpmt USD Equiv]),"$#,##0.00;($#,##0.00)[Red]")

Do you happen to notice my error?
0
 
John TsioumprisSoftware & Systems EngineerCommented:
For this you need conditional formatting
You set "less than " 0 and you change to your liking
0
 
D PatelD Patel, Software EngineerCommented:
Then Try this:

="Total for " & [cboPmtMadeYN] & " (USD Equiv):  " & Format(Sum([Rpmt USD Equiv]),"$#,##0.00;(0)[Red]")
0
 
D PatelD Patel, Software EngineerCommented:
The error was due to wrong value passed in parameter. You don't need to rewrite '$#,##0.00' in parenthesis before [Red].

It will definitely solve your concern.
0
 
pdvsaAuthor Commented:
John, I believe conditional formatting would change the color of the entire text box.  I only need the currency to be red.  

Dhara:  that did not seem to be it either.  

let me know if you have another idea.  thank you
0
 
D PatelD Patel, Software EngineerCommented:
It should work. I tried it.
0
 
pdvsaAuthor Commented:
This is in a report if that matters.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Conditional  formatting affects font weight color and background....so use it as you like
0
 
pdvsaAuthor Commented:
Thank you.  In my report, the red formatting doesnt seem to fire if the value is negative.   I have no issue if i break the text box into its parts and leave the number in its own box.  The issue with RED format not firing for negatives is when I consolidate it all in one text box.

Let me know if you have any other ideas.  Also note my text box is in a report.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
for numbers, the first part of the format code is positive numbers, then negative numbers, then 0 (zero) then null. The data type must be a number, not a string.

"$#,##0.00;[Red]-$#,##0.00";(0);""
0
 
pdvsaAuthor Commented:
Hi Crystal: I seem to be getting a syntax:
(I have pasted your comment from above into the format section in the following):
="Total for '" & [cboPmtMadeYN] & "' (USD Equiv):  " & Format(Sum([Rpmt USD Equiv]),"$#,##0.00;[Red]-$#,##0.00";(0);"")
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
inside a string delimited with double quotes, you need 2 ...
"$#,##0.00;[Red]-$#,##0.00;(0);"""""

Open in new window

 null code is be a ZLS (zero-length string, "") -- alternately you can simply not specify this
0
 
pdvsaAuthor Commented:
Thank you Crystal....
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

the only reason I specified Null code was so you could see where to put some text in (ie: "Fill this out", or whatever) -- leave it off if you are not going to do that
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now