Hello,
On my report in Microsoft Access for all fields I have set-up this code in Control source: - =Replace(Replace(Replace(Format(Sum([NameOfTheField]),"#,###.00"),".",""),",","."),"",",")
and on this way I managed to replace format "123,456.78" to "123.456,78" because I need to have Euro formatting on report.
This code works ok but problem is when some column contains zero value, then I would like to replace "0" with "-" and I don't know how to achieve this?
And help is appreciated and many thanks for prompt replys!
Cheers:)
5 3288
From the AC2003 helpfile on format, by clicking see also I found this on formating numbers:
A user-defined format expression for numbers can have from one to four sections separated by semicolons. If the format argument contains one of the named numeric formats, only one section is allowed.
If you use:- One section only: The format expression applies to all values.
- Two sections: The first section applies to positive values and zeros, the second to negative values.
- Three sections: The first section applies to positive values, the second to negative values, and the third to zeros.
- Four sections: The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null values.
The following example has two sections: the first defines the format for positive values and zeros; the second section defines the format for negative values.
"$#,##0;($#,##0)"
If you include semicolons with nothing between them, the missing section is printed using the format of the positive value. For example, the following format displays positive and negative values using the format in the first section and displays "Zero" if the value is zero.
"$#,##0;;\Z\e\r\o"
My suggestion would be to try like this: - =Format(Sum([NameOfTheField]), "€#,###.##;;-")
Thanks a lot TheSmileyCoder for quick reply!
I tryed with =Format(Sum([NameOfTheField]), "€#,###.##;;-") and when it's 0 value I see "-" which is exactly the way I wanted to be but instead "123.456,78" there is still "123,456.78" and that's a problem.
Then you can still modify the format function to your previous solution. I don't know exactly how the . and , placement of the format function works. I know its somehow related to the REGIONAL settings of the computer your working on. - Replace(Replace(Replace(Format(Sum([NameOfTheField]),"€#,##0.00;;-"),".",""),",","."),"",",")
Hello SmileyCoder, in my first post I have written wrong code, sorry for that, this is the correct one:
=IIf(Sum([MyField])=0,"-",Replace(Replace(Replace(Format([MyField]],"#,###.00"),".","_"),",","."),"_",","))
and it works good but I have this issue: http://img829.imageshack.us/img829/5130/12321.gif
so on some fields I have these wierd values like ",00" or ",10" ?
I hope you can help me with this issue.
From the behavior shown in the screenshot im guessing you have rounding errors somewhere. Im guessing the fields showing up as ,00 contain values that are smaller then [0,005]
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike MacSween |
last post by:
tblCourses one to many to tblEvents.
A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.
At...
|
by: George |
last post by:
I have a Microsoft Access report and I have automated
this report using C#. When the report opens (via
DoCmd.OpenReport) I am confronted with several prompts
for criteria (example: "Please enter...
|
by: George |
last post by:
I have a Microsoft Access report and I have automated
this report using C#. When the report opens (via
DoCmd.OpenReport) I am confronted with several prompts
for parameters (example: "Please enter...
|
by: George |
last post by:
I have a Microsoft Access report and I have automated
this report using C#. When the report opens (via
DoCmd.OpenReport) I am confronted with several prompts
for parameters (example: "Please enter...
|
by: bobc |
last post by:
Any help appreciated with this problem --
I have a Microsoft Access 2002 application. The Access
mdb has a refence to DAO 3.6 (not ADO). A form is bound to
a linked table in a second mdb. In the...
|
by: vlado.eltrade |
last post by:
Hi there.
I have a problem with wrong MS Access report Pages property after
hiding the page footer section on the report. The pages propery reffers
to the max number of pages as the section was...
|
by: flomaster |
last post by:
Hi,
I have created a c# application which uses Microsoft Access Reports. The application works ok on my laptop but when I installed it on other pcs ( more than 5 ) the following problem appears.
...
|
by: MDestructive |
last post by:
First, let me say that VBA is not my strong point but I have been bestowed with creating a duplicate database of a former employee who sabotaged the original. And everyday this isn't working is...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| | |