In a text field, how do you insert a space as a thousand separator ?

[antonx]

Hi,

I have Crystal Reports 8.5 and for our french clients we need to put a space as a thousand separator and a comma as a decimal separator in a field that is defined as TEXT. For our english clients, we use commas as thousands and a point as a decimal separator. How can we do this in a TEXT field?

This is how it should display FRENCH -> 2 077,96 $
ENGLISH -> $ 2,077.96

Remember that the 2077.96 is a text field not numeric.

I tried the following formula for decimal separator but I can’t figure out what to put for the thousand separator:
Local NumberVar NumericValue;
Local NumberVar BusinessCommission;
Local NumberVar CurrDevComm;

NumericValue := BusinessCommission + CurrDevComm;

if {@Language} = "F" then
ToText(NumericValue, 2, " ", ",") + " " + "$"
else
"$" + ToText(NumericValue, 2, ",", ".");


Thank you in advance.

 

[vidru]

You should be able to do it using custom number formatting instead of fighting with strings. Just create a formula that returns the result of your two fields as a number (e.g. 1254.22). Then use customize the Decimal Separator, Thousands Separator, Currency Symbol, and Currency Symbol Position. The formulas for each would look like so:

1) Decimal Separator:
if {@Language} = "F" then "," else "."
2) Thousands Separator:
if {@Language} = "F" then " " else ","
3) Currency Symbol
if {@Language} = "F" then " $" else "$ "
4) Currency Symbol Position:
if {@Language} = "F" then crTrailingCurrencyOutsideNegative else crLeadingCurrencyOutsideNegative

-dave

 

[synapsevampire]

Dave: Show the formula for returning a string with decimals as a value, you might be surprised.

When I try in CR 9 with SP 3 using:

tonumber(1000.34), I can't get it to return the decimal places.

I worked most of it out using the picture function, but I have to leave.

-k

 

[vidru]

Hmmmm...

k,

Using CR 9.2.3.970 (I think I put SP3 on here), these 3 formulas all return 1,125.12:

val("1125.12")
tonumber(1125.12)
tonumber("1125.12")

I'd also tested my first reply with CR 8.5 before I posted it.

What's up with that?

-dave

 

[Madawc]

A completely different approach is possible, based on the formatting. This allows you to define decimals and thousand-spaces as something else.

The problem, of course, is that you have different clients with different needs. But if the reports are running on different machines, you might be able to control it by setting the defalts. That's the [Fields] tab from [Options] from the [File] menu. With luck, values will go automatically to the default on that machine. (It has a way of happening when people don't want it to.)

Madawc Williams (East Anglia)

 

[synapsevampire]

Madawc: It's not that simple.

Anwyay, hopefully Dave's solution worked, otherwise it's a pain to work out, I've had to do it in the past...

-k