Compute Ranges with Bad Data

[MikeCopeland]

Using CR XI...
The SQL database I'm using has a VarChar field I must compute value ranges on, but the data contains values I can't work with. The field is called PlanCompleted and represents (yes) the percentage of education that was completed. However, the fact that the field is VarChar has allowed the population of really bad data: "-100%", "38.5", "N/A", "100000%", " ", etc., as well as "normal" a/n values of "10", "45", "99", etc.
Is there a way I can convert the data to a numeric value and use that result for comparison? I don't know enough about using temporary values and intermediate computations in formulas to write the code I;d probable need, so I'd also need an example. TIA
Here's the formula I started with before I discovered the data really stinks:
// @numGrad60_69 - counts the PlanCompleted values of 60-69 MRCopeland 10/21/2008
WhileReadingRecords;
if(({CBSIntakeDischarge.PlanCompleted} >= "60") and ({CBSIntakeDischarge.PlanCompleted} <= "69")) then 1 else 0

 

[Charliy]

Two commands that would be useful to you are ISNUMERIC and VALUE.

First have a formula that pulls out the numeric value if it's good:
//@numscore
IF ISNUMERIC({CBSIntakeDischarge.PlanCompleted}) then VALUE({CBSIntakeDischarge.PlanCompleted}) ELSE 0

Then when you're grading them, use formulas like:
IF {@numscore} IN 60 TO 69 THEN 1 ELSE 0

or better yet use it as the evaluate formula in a Running Total.

 

[lbass]

What kind of calculations? You could use:

val({CBSIntakeDischarge.PlanCompleted})

... which would return 0 for "N/A", 100 for "100%", or 45 for "45".

if val({CBSIntakeDischarge.PlanCompleted}) >= 60 or
val({CBSIntakeDischarge.PlanCompleted}) < 70 then //etc.

-LB

 

[Charliy]

Oops, yes I meant VAL when I typed VALUE.