select only strings that can be converted to numbers

[Alicia159]

First of all, I am running Crystal 9 with SQL 2000.

I have a field in a vendor's database called "crscore" that is integral in a variety of my reprots. The field used to be numeric, and has been converted to a string to accomidate the very few people who do not have a score, but instead display an error message. Now all of my reports bomb even when I try to convert the field to a numeric, because the system finds the non-numeric values.

Basically I need to find a way to tell the system to ignore all strings that are not composed of numbers. Is there a way to do this? I can't filter on the non-numeric error message, because it is varied.

 

[Katy44]

Look at using "NumericText" - hope this works!

 

[synapsevampire]

Try:

if isnumeric({table.field}) then
val({table.field})
else
0

Then you can use it as a numeric.

Crystal won't allow output in both numeric and text, so if you need to display both from one field then you'll have to either use the text, or convert as in the above.

-k

 

[Katy44]

What's the difference between that and numerictext? They look identical? Is it the output value?

 

[dgillz]

NumericText() is a boolean formula; it returns true or false.

Val() will return the value of the text, or error out if the text is not numeric. Hence the need to test the values with the if-then first.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[Katy44]

Thanks - but surely then isNumeric() and NumericText() do the same? Or am I missing something obvious?

 

[lupins46]

No, you're missing nothing - they are equivalent.