Solved

Format as Currency

Posted on 2016-09-14
18
37 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
  • 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 6

Expert Comment

by:DPatel
ID: 41797760
Try with this:

="Total for " & [cboPmtMadeYN] & " (USD Equiv):" & format(Sum([Rpmt USD Equiv]),"Currency")
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 6

Expert Comment

by:DPatel
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 13

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 6

Expert Comment

by:DPatel
ID: 41797781
Then Try this:

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

Expert Comment

by:DPatel
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 6

Expert Comment

by:DPatel
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 13

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 19
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 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 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 19
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now