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
pdvsaProject financeAsked:
Who is Participating?
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote 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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
D PatelD Patel, Software EngineerCommented:
Try with this:

="Total for " & [cboPmtMadeYN] & " (USD Equiv):" & format(Sum([Rpmt USD Equiv]),"Currency")
0
 
D PatelD Patel, Software EngineerCommented:
Shaun Kline is right. You are missing parenthesis for Sum Function
0
 
pdvsaProject financeAuthor 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
 
pdvsaProject financeAuthor 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
 
pdvsaProject financeAuthor 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
 
pdvsaProject financeAuthor 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
 
pdvsaProject financeAuthor 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
 
pdvsaProject financeAuthor 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
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.