ToText(x,y,z,w,q)

[N11689]

I am trying to convert a currency field ToText. I don't want it to display the currency symbol.

I have the formula set as follows:
ToText({Currency.Field},'#','2',',','.')
This should display the field carrying it to two decimal places with a comma(,) as a thousands separator and a period (.) as a decimal separator.

What is happening is a figure of 1500.00 displays as
$1500

I want it to display as 1,500.00

According to this ToText(x,y,z,w,q) formula, I would expect 1,500.00

Does anyone know why this ToText conversion doesn't work properly?

 

[mbarron]

There is nothing in the help that indicates that using the ToText(x,y,z,w,q) will eliminate the $. Although you would be led to believ that by using the text string format option it would eliminate the $. Crystal assumes that you wouldn't want to get rid of the currency symbol. You have to do it manually.


right(totext({currency.field},2,',','.'),len(totext({currency.field},2,',','.'))-1)

Mike

 

[lbass]

If you need it to be a text field, just use:

replace(totext({currency.field}),"$","&quot

Or if you have 8.0 or lower and have trouble with the replace function, use:

trim(replace(totext({currency.field}),"$"," &quot)

But to get the display, you could just convert it to a number field:

tonumber({currency.field})

In both cases a comma separator and two decimal places are the default.

-LB

 

[mbarron]

In both cases a comma separator and two decimal places are the default.

This is only true if you haven't changed your defaults for numbers or currency to something other than with a comma and 2 decimals.

This would be changed under File / Options... on the fields tab. You can change your currency field to not display the $ by default. Then when you do the totext on the currency field the $ won't display. The change, however is universal.



Mike

 

[lbass]

Mike,

Wouldn't you agree at least that the comma separator and the two decimal places are the defaults of the defaults?

-LB

 

[mbarron]

Yes -- yes I would.

This is only true if you haven't changed your defaults for numbers or currency to something other than with a comma and 2 decimals.

would have better as

This is only true if you haven't changed the defaults for numbers or currency to something other than with a comma and 2 decimals.


Mike

 

[N11689]

Thanks for your input. I have another question with regard to getting rid of the currency symbol manually. Our options, in File/Options, for currency is to disable the currency symbol. We don't want it to print at all. However, the ToText() seems to ignore that and use the currency symbol based on the Regional Settings of the machine. If this is the case, to manually remove the currency symbol, we need to know where the currency symbol resides. The user may choose to print it at the end of the currency figure (display as 1,500.00$ vs. $1,500.00) or with a space between the currency symbol and the dollar amount. So, how do you format that in the formula so Crystal can remove it for any of the regional settings scenarios.

And, I haven't tried the replace function either, but if the user is using a foreign currency, how can we use the replace function to accommodate any currency?

We have users both here and abroad that run the same reports. This is what I am concerned about.

Thank you.

 

[lbass]

The replace function will search the string for the symbol wherever it is located. Since you might have a space between numerals and the currency sign, and since you haven't mentioned what version of CR you are using, try using the following which will remove spaces at either end of the field:

trim(replace(totext({currency.field}),"$"," &quot)

-LB

 

[mbarron]

All of the machines would have to have the currency symbol turned off. Changing on the creater's machine won't carry it over to the distributed machines.

Here is a formula that would work if the currency sybol was always first.

whileprintingrecords;
local stringvar sym:=left(totext(CCur (00.00)),1);
stringvar amount:=totext({currency.field});
trim(replace(amount,sym," &quot)

Since it's not.... This one checks both the front and back for a currency symbol. It will strip the symbol if it is there. If it is not it will disply the field as is.

whileprintingrecords;

local stringvar amount;
local stringvar value:=totext({currency.field});
if isnumeric(left(value,1)) then amount:=left (value,len(value)-1) else
if isnumeric(right(value,1)) then amount:=right(value,len(value)-1) else
value

Mike