toNumber({table.stringIDnumber}) give error "string is non-numeric"

[tektipsMem]

CRXI; WIN XP

Trying to change a number string to number in order to calculate:

if (toNumber({table.stringIDnumber})mod 111111111 =0)then ({table.stringIDnumber} else "-1"

gave "The string is non-number" error, why and how to fix?

TTM

 

[synapsevampire]

Try:

if isnumeric({table.stringIDnumber})then
val({table.stringIDnumber})
else
-1

There were 2 main reasons why your formula failed, you can't convert unless it contains a number, so you need to check first, then you also had a numeric as the output if it's numeric, otherwise a string, which is also a no-no.

-k

 

[tektipsMem]

Thanks for quick response.

{table.StringIDnumber} is a numeric string. IsNumeric function gives error of requiring a number. That is exactly what I want - to convert the string into number. I kept part of mine and tried part of yours:

if (toNumber({table.StringIDnumber})mod 111111111) = 0
then val({table.StringIDnumber}) else -1

That seems to work but val({table.StringIDnumber}) converted my 9 digit 0's to 0, ie. I want 000000000, instead of 0.

How do I achieve that?

TTM

 

[tektipsMem]

Oops, when I refresh it, it gave the same "The string is non-numeric" error. So, is there a way to convert a string number into number number??

TTM

 

[dgillz]

Use the trim() function to get rid of trailing or leading spaces.

Also verify the database.

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

http://www.gainfocus.biz

 

[tektipsMem]

Sorry to reply late. Thanks synapsevampire, retype your suggestion works when I use it this way:

if isnumeric({table.stringIDnumber})then
val({table.stringIDnumber})mod 111111111

Good tips dgillz, will be handy to use in future.

TTM