?
Solved

Format as Currency

Posted on 2016-09-14
18
Medium Priority
?
46 Views
Last Modified: 2016-09-16
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
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41797753
You are missing an end parenthesis for your Sum function.
format(Sum([Rpmt USD Equiv]),"Currency")

Open in new window

0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41797757
Depending on your version of MS Access you can also use the FormatCurrency function.
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41797760
Try with this:

="Total for " & [cboPmtMadeYN] & " (USD Equiv):" & format(Sum([Rpmt USD Equiv]),"Currency")
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 7

Expert Comment

by:D Patel
ID: 41797761
Shaun Kline is right. You are missing parenthesis for Sum Function
0
 

Author Comment

by:pdvsa
ID: 41797765
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
 
LVL 18

Expert Comment

by:John Tsioumpris
ID: 41797768
For this you need conditional formatting
You set "less than " 0 and you change to your liking
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41797781
Then Try this:

="Total for " & [cboPmtMadeYN] & " (USD Equiv):  " & Format(Sum([Rpmt USD Equiv]),"$#,##0.00;(0)[Red]")
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41797790
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
 

Author Comment

by:pdvsa
ID: 41797797
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
 
LVL 7

Expert Comment

by:D Patel
ID: 41797804
It should work. I tried it.
0
 

Author Comment

by:pdvsa
ID: 41797805
This is in a report if that matters.
0
 
LVL 18

Expert Comment

by:John Tsioumpris
ID: 41798036
Conditional  formatting affects font weight color and background....so use it as you like
0
 

Author Comment

by:pdvsa
ID: 41798090
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
 
LVL 22
ID: 41798268
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
 

Author Comment

by:pdvsa
ID: 41798385
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
 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41798400
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
 

Author Closing Comment

by:pdvsa
ID: 41801655
Thank you Crystal....
0
 
LVL 22
ID: 41801662
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

765 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