ToNumber - Crystal XI - convert string to number syntax

[Former Member]

i have a field which is a string in the database, but i want to covert to a number so that i can format decimals etc.

i have tried

toNumber {table.field}

but I get "the string is not numeric" - so not all are numeric or there are nulls?

then i tried the

If NumericText ({table.field}) Then

ToNumber ({table.field})

Else

0

but I get unwanted 0 showing up on the report.

i have tried

If NumericText ({table.field}) Then

ToNumber ({table.field})

Else

({table.field})

but then I get "a number is expected here"

So what is the most efficient way to achieve this? i've read the posts about similar questions but not getting the answer that works for me.

thanks for all help! Jenn

[Former Member · Accepted solution]

When i just put the field in the report, the items that come up as zeros are either blank or a line of dashes. -

---

instead of else 0 is there a way to achieve else " " blank?

jc

[Former Member · Accepted solution]

JC,

Just remove the "Else 0" part and this will sort of insert a NULL, if the field cannot be converted to a number.

You then have to format your Formula item on the layout of the report to handle zero values as <supress> or what ever you

want the "null" values to appear as.

Hope that makes sense.

Darren

[Former Member · Accepted solution]

Thank you Darren. that did the trick. much appreciated.

[Former Member]

Jenn,

What do you want to return if the value is not Numeric?

Your first bit of formula should give you what you are wanting:

If NumericText ({table.field}) Then
      ToNumber ({table.field})
Else
     0

If you place an un-formatted version of {table.field} next to the above field, what are its values when "0" is returned.

This will give you an idea of what items are in the database that are causeing the NumericText to fail.

Maybe try IsNumeric(str) and see if that is different.

Maybe check the database locale settings compared to the CR settings, if decimal indicators are different you might get issues.

Also make sure you trim the data first, trailing or leading white spaces will cause the NumericText to fail.

So you might have to do some formatting or the text before you check if it is numeric.

To be safe maybe try

StringVar myNumericString

myNumericString := Replace({table.field}," ","");
myNumericString := Trim(myNumericString);       // Above will do this but just for fun 

 
If NumericText(myNumericString) then 
	ToNumber(myNumericString);
Else 
	0;

Hopefully that helps.

Darren

[Former Member]

Jenn,

Are you not able to do an data manipulation on the database side, before it gets to the report?

Just an option.

Darren

[Former Member]

Hi Darren,

Would that I Could. I'm afraid it is not an option for me. I have no access to the database tables.

JC