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

[adi2011]

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

 

[Galaxiom]

One easy way is to enter the Format property of the control as:
;;-

Edit: Oh no that only works on numeric values and the Format function had changed it to a string.

Use IIF(fieldname=0,"-", Replace(etc...))

 

[adi2011]

Hello GalaxiomAtHome, I tried to Use IIF(myfieldname=0,"-", Replace(etc...)) but at the end total amount looks like this: 123.45678 and it should be of this format "123.456,78"

 

[Galaxiom]

I didn't look at the detail of your Replace and Format expression but the IIF shouldn't make any difference to its result.

Actually what is that you are doing with the replace? It doesn't appear to be correct as it is displayed on my browser. You seem to be replacing the dot with nothing.

 

[adi2011]

Hello Galaxiom, I made mistake in my first post, this is the tight code which actually replaces decimal separator with comma (,) and thousand separator with dot (.)

Replace(Replace(Replace(Format(123456.78,"#,###.00"),".","_"),",","."),"_",",")

Following your suggestion now I have this code:

=IIf(Sum([MyField])=0,"-",Replace(Replace(Replace(Format([MyField]],"#,###.00"),".","_"),",","."),"_",","))

and it works very good.

Thanks again and cheers!

 

[adi2011]

It looks like I din't completley solve my issue yet)

It all works works good but looking through my report which contains many Sum fields, I have this issue:

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.

 

[adi2011]

It looks like my problem isn't copmletely solve yet)

On my report all fields are Sum values and there is a lot of data so looking through my four-page report I noticed some wierd values for some rows:

img829.imageshack.us/img829/5130/12321.gif

even with the fact that all columns have unique formula/code:

=IIf(Sum([MyField])=0,"-",Replace(Replace(Replace(Format([MyField]],"#,###.00"),".","_"),",","."),"_",","))

but problem are these these wierd values like ",00" or ",10" , how to get rid of them?

 

[Galaxiom]

I expect there would be some rounding errors causing this.
Some very small values are not equal to zero.

Change the IIF test to a range for example:
IIF (fieldname < .01, etc)

Or use the Round function
Round(fieldname,2)

Remember that the Single and Double variables work in binary and so can get unexpected rounding errors on divisions.

Currency or Decimal datatypes are used for more precision.

 

[adi2011]

It looks like my problem isn't completely solve yet

On my report all fields are Sum values and there is a lot of data so looking through my four-page report I noticed some wierd values for some rows:

img829.imageshack.us/img829/5130/12321.gif

even with the fact that all columns have unique formula/code:


=IIf(Sum([MyField])=0,"-",Replace(Replace(Replace(Format([MyField]],"#,###.00"),".","_"),",","."),"_",","))


but problem are these these wierd values like ",00" or ",10" , how to get rid of them? Basically if these fields are equal to zero (0) there should be "-" and if they are not then there should be some number with clear/normal format.

 

[adi2011]

It looks like I made mistake on some fields and that's why ",00" was there

I have problem only with this now:

img84.imageshack.us/img84/7041/unled2f.gif

and I am not sure why doesn't it show "0,88" instead of ",88" or "0,10" instead of ",10"? I assume it has some problems with showing values which are smaller then 1 and I am not sure can I solve this part with IIf option because my client wants everything to be perfect so there should be "0,88" instead of ",88"

How can I acomplish this?

 

[Galaxiom]

Hash is a placeholder for an optional character. Place a zero to force it.

"#,##0.00"

 

[adi2011]

Thanks a lot Galaxiom for your help, I really appreciate it.

Cheers