Converting a string to a number

[asbach]

Hi all!

I have a problem: In my database there is a field, which is actually a value of money (like 20.00 or -245.9555 or 1340 etc). Somehow it is determined to be a 'string' evethough it should be 'number'.

Now, I try to convert it to be a number using 'to number'-function. My Crystal Reports 8.5 with SQL 7.0 does not approve that and generates a error message "the string is non-numeric...".

So, I created a simple test and it ignores all values, which are form xxx.yy (e.g. 120.55) and takes all '20' or -'2340' -values -i.e. all values without fractions.

What should I do to be able to convert all fields to number in order to subtotal and grand total them?

Thank you all in advance!

Asbach

 

[dgillz]

My guess is that there are trailing or leading spaces in this 'string'. Tyr this:

ToNumber(Trim({YourField}))

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[ns130]

Just a note: You should always use ToNumber({X}) with NumericText({X}). This function will return true if the string can be converted to a number and prevent the error you are getting:

If NumericText ({X}) Then
ToNumber ({X})
Else
0

 

[asbach]

Thanks ns130 andd gillz!

However, the final solution came using "replace"-function, where I replaced all "." to ",". By that I could just use "tonumber" in a simple way without if-else operator.

Regards

Asbach

 

[dgillz]

Why would replace() work for you like that? I am very curious.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[asbach]

Hi dgillz!

As I explain, I had a field, where all numbers were form 'xx.yyyy'. Somehow in database the field was non-numeric string and therefore I had to convert it to numeric. By using "replace" I changed all dots to commas ('xx.yyyy' to 'xx,yyyy') and then used "ToNumber".

If you meant that the behaviour of "Replace"-function was odd, I have no explanation. In the end I am pleased I could continue my work.

Rgds

Asbach

 

[Naith]

Is that an accurate fix, interchanging decimals and commas? I mean, 245.541 and 245,421 are not equivalent values.

 

[asbach]

Hi Naith.

I have accomslihed some testin and at least it works...


Asbach

 

[rosemaryl]

Dear Naith,

Well it depends on where the people looking at the values are from.

It is common in Europe for the decimal character to be a "," and the thousands separator to be a "."

It seems to me in his database they stored the values using the european style and for some reason the dba chose a varchar field rather then a number field.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[asbach]

Dear all,

I have had a little bit time to investigate this problem.

I have a simple test as 'ns130' suggested. That works well. I have learned that all values, which have fractions (like "130,44") are being converted to "0,00" but all values, which are not having decimal values (like "156,00"), pass the test well and output is "156,00" as it should.

Why these decimal, fractional values are suddenly somewhat bad values and are being ignored? What should be done in order to avoid this?

Regards

Asbach

 

[GJParker]

Check the regional settigns on your PC, which character is being used as decimal seperator ? This should be set the same as the database.

If the decimal seperator is a dot "." then 130,44 is not numeric where as 156 is

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[asbach]

Thanks's Gary.

There was one dot, which was then changed to comma. That helped, although all calcultations have to be cross-checked.

-Asbach