Former Member

# Formula Help

Hello,

I am trying to make a formula and need some help. Here is what i have so far:

This is taking the total value of a client's investment and dividing it by the amount they invested to get a gain/loss percent.

I check the formula in crystal reports and it says it has no errors. However, when I try to run the report from our database it gives an error message saying that there is a format issue in a formula. I know it's not any of the other formulas giving me this error because when I delete this one it runs fine. The TotValue field is a currency field and the F26Val is a text field if that would be the problem but to my understanding the ToNumber function should convert the text to number so I can do this. I Would really appreciate some help on this.

Also, I am trying to do a SUM of the F26Val Field and was wondering how to go about that.

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Sep 22, 2008 at 04:41 PM

Also, I am having trouble with the same type of formula. I want to subtract the total ammount invested by the current value to get a gain loss in dollars. Here is what I have:

When I check it in Crystal Reports it says that it's fine but when I run it in our database it gives an error. Again, the F18 field is a text field which is why the tonumber is there.

Any help is greatly appreciated!!!!!

10|10000 characters needed characters exceeded
• Former Member Former Member

Robert,

We may be making this a little bit more complicated than it need to be. It seems unlikely the F18 will ever be less than 0 (it's the amount invested).

Just drop the isnumeric and make it > 0.

Jason

• Posted on Sep 12, 2008 at 04:40 PM

Robert,

A couple of things to try;

1) try CCur to convert the F26Val field to a currency rather than a number.

2) create a check in the formula that the F26val field isnumeric

3) create a check in the formula for 0 or null values for F26Val

So something like this;

if isnumeric(({ProdInterim.F26Val})) and not(isnull(({ProdInterim.F26Val}))) and ({ProdInterim.F26Val}) <> 0 then

10|10000 characters needed characters exceeded
• Former Member

I Tried that and when I check it it gives me an error saying: "A number, currency amount, boolean, or string is expected here." The cursor is put right after the "if" and right bevore "isnumeric"

if isnumeric(({ProdInterim.F18Val})) and not(isnull(({ProdInterim.F18Val}))) and ({ProdInterim.F18Val}) 0 then

Any further help would be greatly appreciated!

• Posted on Sep 12, 2008 at 03:57 PM

Hi Robert,

Since your question is specifically about CR formulas, I've moved it into the CR design forum.

Cheers,

Steve