Solved

Format as Currency

Posted on 2016-09-14
18
41 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 16

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 16

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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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