Trying to calculate a formula based on a text field

[CrystalKim]

I am using Crystal 8.5

I currently have a table that has fields named PreviousORR and CurrentORR. Both of these are number fields but are built as text in the database I get the information from. I tried to convert the field to a number but whenever I do I lose the decimal point and subsequent # which is very important for this field.

I have created a formula that compares both fields and, depending on the result comes up with a name.

Example:
If {QueryRiskRatingChanges.Current ORR}>{QueryRiskRatingChanges.Prior ORR} then
"Downgrade"

else

"Upgrade"

This formula works fine except for one rating which is 10.5. I am assuming that is because the field is read as text by Crystal, so it is reading the rating as a 1 instead of a 10.5.

Is there anyway I can correct this? I am ok with simple formulas but tend to get stuck when it starts getting complex.

Any suggestions would be most appreaciated

Thanks!

 

[synapsevampire]

Try posting technical information as there are a myriad of reasons why this might occur.

Database/connectivty used
Example data
Expected output

For instance if you use the Oracle supplied ODBC driver instead of the Crystal supplied ODBC driver, then you might lose precision.

If you're using a formula which loses precision, post what you tried, stating that it loses precision is helpful, but it doesn't help to understand why.

One method might be to use a SQL Expression, but again, this is database dependent, which is critical to know when resolving data issues.

The nexxt step is to trade your dba for a city rat, and shoot the rat. Storing values as text speaks ill of their skillset and will continue to cost the company $.

Another solution is to build a View/Query on the database which CASTs/CONVERTs the field to it's proper data type.

-k

 

[CrystalKim]

The database is MS Access 2000

Some expample data would be

Customer Name, Current ORR, Previous ORR, Current Balance
ABC Company------------10.1-----------5.5--$5,000
123 Inc. 6.1-----------7.8--$12,000

What I have created is a report that groups the data on my report based on the formula. The expected output would look something like this.

Upgrades

Company Name, Current ORR, Previous ORR, Current Balance
123 Inc.--------------6.1-----------7.8---------$12,000

Downgrades
Company Name, Current ORR, Previous ORR, Current Balance
ABC Company----------10.1-----------5.5-----------$5,000

thanks for your reply. If I need to give you anymore information please let me know.

 

[synapsevampire]

This seems very simple to resolve at first blush, create a Query on the Access database and change the data type(s) to numeric within the query, then use the Query as the data source for the report.

-k

 

[CrystalKim]

I tried changing the datatype within Access at the query level but it didn't work.

I right clicked on the field itself (in the query builder)and then went to properties and it wouldn't give me the option of changing it to a number. Is there another way around this in Access that you are aware of?

Thanks again

Now I see why text fields can be such a pain!

 

[synapsevampire]

Try the val(table.field) function in the query.

-k

 

[CrystalKim]

I wound up writing a funtion in a module with the help of the person who inherited the database.

Thanks for your help and prompt attention to my dilema. .