String to Number Value

[tmozer]

A huge table I am working with contains lab test results. One of the fields contains actual result values for various tests and is a string field, since it is a catch-all for varied types of data.

The tests I am interesting in tracking have a number in this string field indicating how many positive results were obtained for a particular sample. I am using ToNumber({Results.Date}) to convert the sting to a value for summing. I have filtered for blank fields, but apparently a very few of the records have something other than a NULL or a numeric string in this field. Therefore my Sum function bombs out part way through the data.

How can I trap for a string value that is not a numeric string?

 

[lbass]

Substitute the following for {table.string}:

if isnull({table.string}) or
trim({table.string}) = "" or
not (isnumeric({table.string})) then
0 else tonumber({table.string})

-LB