Convert String to a Number/Cur?

[EastCoast]

Issue: I am trying to create an Average field (in the group footer)for a field called "Evaluation Scores" (which is in the Detail section).

Problem: The field is a String type (the choices on the eval are 1,2,3,4,5 and "N/A" - the evil string part) so I cannot run any summary operations on it. I have tried a formula with CCur but no go. I do not have access to the tables to change the data type. Can I change the data type in Crystal? Any ideas?

 

[synapsevampire]

Create a formula of:

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

Now use the formula.

Posting your software version and the database used generally nets more efficient responses, and it's something that should be included with any post for any software product.

-k

 

[EastCoast]

Thank you. I have Crystal 8.5 and it is a Sybase Database I am connecting to.

 

[EastCoast]

I used your code but didn't get it to work.

if isnumber({STUDENTEVALS.EVAL_ANSWER}) then
val({STUDENTEVALS.EVAL_ANSWER})
else
0

 

[synapsevampire]

State what it is that doesn't work, such as an error message or it rpovied incorrect results, or you got your fingernail caught in the keyboard and you're trapped for life now...

We don't know why you think that it doesn't work.

-k

 

[EastCoast]

I created the field, put it into the details section to test and got the following information dialog box (the only button this box gave me was the "OK" button to acknowledge the message below:

"A number, currency amount, boolean, date, time, date-time, or string is expected here."

It let me go to Print Preview of the report, but no data whatsoever showed (snowstorm of white), then it brought me back to the formula editor giving me that same message.

 

[synapsevampire]

Right click the {STUDENTEVALS.EVAL_ANSWER} field and select browse data to learn it's data type and post back.

If it contains "N/A", then it should be a string and the formula should work.

If it's anotehr data type, such as a numeric, then it can't contain "N/A".

-k

 

[EastCoast]

That field is a string field - it had a [25] which I assume is the field size. It has values in it of 1,2,3,4,5 "Fast", "Slow", and "N/A".

 

[synapsevampire]

Very strange, OK, let's try:

if not(isnull({STUDENTEVALS.EVAL_ANSWER}))
and
isnumber({STUDENTEVALS.EVAL_ANSWER}) then
val({STUDENTEVALS.EVAL_ANSWER})
else
0

Also, where are you trying this code?

You sould be right clicking formulas and select NEW.

-k

 

[EastCoast]

I put in exactly what you have. Not working still. I get the same error message as before. To create this formula I created a new field from the field list. Perhaps my table links that I created when I chose my data source is the problem? I appreciate the help, I can't think of what else I'm doing wrong.