473,320 Members | 2,117 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with Euro formatting (123.456,78) on Microsoft Access Report

Hello,

On my report in Microsoft Access for all fields I have set-up this code in Control source:

Expand|Select|Wrap|Line Numbers
  1. =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:)
May 1 '11 #1
5 3288
TheSmileyCoder
2,322 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. =Format(Sum([NameOfTheField]), "€#,###.##;;-")
May 1 '11 #2
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.
May 1 '11 #3
TheSmileyCoder
2,322 Expert Mod 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. Replace(Replace(Replace(Format(Sum([NameOfTheField]),"€#,##0.00;;-"),".",""),",","."),"",",")
May 2 '11 #4
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.
May 2 '11 #5
TheSmileyCoder
2,322 Expert Mod 2GB
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]
May 2 '11 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

8
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...
0
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...
0
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...
0
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...
2
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...
0
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...
0
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. ...
5
MDestructive
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...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.