Search code examples
crystal-reportscrystal-reports-2013

Error when converting string number to decimal in Crystal Reports: "The string is non-numeric"

Problem description

In my report I get amounts in strings. The formatting is pretty unusual:

  1. "" - is zero amount
  2. "(200.00)" - is negative amount
  3. "1,234.56" - is positive amount

I want to convert those strings to numeric values in more convenient way:

  1. 0.00
  2. -200.00
  3. 1234.56

First I am doing some preformattings of the string amount:

local stringvar amount := Trim({PLD__ITEMS.F_18});

if amount = ''
    then amount := '0.00'
;

amount := Replace(amount, "(", "-");
amount := Replace(amount, ")", "");
amount := Replace(amount, ",", "");
amount := Replace(amount, " ", "");

Then I wanted to convert string into number using ToNumber or CDbl methods, but both result with the same error

// "The string is non-numeric"
//ToNumber(amount)

// "The string is non-numeric"
//CDbl(amount)

I has no idea what could possibly cause this error.
I can't find any corrupted string in the formatted amounts...

The string is non-numeric

Questions

  1. How could I fix my string amount to make ToNumber and CDbl works fine?
  2. How can I convert string amount to decimal number without using ToNumber or CDbl methods?

If there was only displaying issue, I could use strings as there are, but I need to do some calculations with those amounts so I have to use numeric values there.

Testing unexpected characters in string amount

I prepared specific test to see if any of string amount value has unexpected character inside, but all results of below comparision returned True

// ---- test ----

amount := Replace(amount, "0", "");
amount := Replace(amount, "1", "");
amount := Replace(amount, "2", "");
amount := Replace(amount, "3", "");
amount := Replace(amount, "4", "");
amount := Replace(amount, "5", "");
amount := Replace(amount, "6", "");
amount := Replace(amount, "7", "");
amount := Replace(amount, "8", "");
amount := Replace(amount, "9", "");
amount := Replace(amount, ".", "");
amount := Replace(amount, "-", "");

// has not unexpected characters
amount = ''

// ---- end test ----

Testing convertion

I tested explicit converion of string with point as decimal separator and again error occured (what is strange for me)!

enter image description here

I am using Crystal Reports 2013

Solution

  • My idea to solve this problem was to split the string amounts on integer and fractional part, convert them to numbers separately and then add both to get the value.

    local stringvar amount := Trim({PLD__ITEMS.F_18});
local stringvar intAmount := '0';
local stringvar decAmount := '0';
local numbervar result;
local numbervar decimal;


if amount = ''
    then amount := '0.00'
;

amount := Replace(amount, "(", "-");
amount := Replace(amount, ")", "");
amount := Replace(amount, ",", "");
amount := Replace(amount, " ", "");

if InStr(amount, '.') > 0
    then (
        intAmount := Left(amount, InStr(amount, '.') - 1);
        decAmount := Right(amount, len(amount) - InStr(amount, '.'));
    )
    else intAmount := amount
;

result := ToNumber(intAmount);
decimal := ToNumber(decAmount) / 100;

if result > 0
    then result := result + decimal
    else result := result - decimal
;

    Although this tricky solution works fine, my questions are still open for ideas and/or advices.

    updated

    Fix solution for negative numbers - fractional part should be substracted from integer part