ToNumber() vs VAL():

Sunday 1 July 2007 @ 12:16 pm

I find many formulas using the ToNumber() function in Crystal Reports formulas to convert numeric text into a true numeric value. Usually it is because they don’t know the advantages of using the Val() function. While ToNumber() works, it will generate an error if the string has any characters, punctuation or symbols in it. The usual solution is to test the value by saying:

If NumericText ({field}) then ToNumber ({field}) else 0

However, even this doesn’t work well if characters come after the numerics. For instance street addresses that start with numbers, or a list of part numbers like this:


All the values above that end with a letter would return a zero with the formula above. The Val() function is more forgiving and will stop when it reaches the alpha character or symbol. It then returns the value of the numbers up to that point. So the list of parts above would be converted to their numeric values and all the trailing letters would be ignored.

I have found only a few rare reasons for using ToNumber() instead of Val().

1) ToNumber() can work with Booleans and currency values in addition to strings. Val() only work’s with strings
2) ToNumber() can also convert the following strings, while Val() will return zeros for both:

ToNumber ( “(123)” ) becomes -123
ToNumber ( “$123” ) becomes 123

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

One Response to 'ToNumber() vs VAL():'

  1. Vinay - June 15th, 2011 at 2:13 am

    Nice work Buddy…
    Thanx a lot…

Leave a Reply

Recrystallize Pro