Formula Help

[Former Member]

Hello,

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

{TranLink.TotValue}/ToNumber ({ProdInterim.F26Val})

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.

Thanks in advance!

[Former Member · Accepted solution]

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:

{TranLink.TotValue}-ToNumber ({ProdInterim.F18Val})

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!!!!!

[Former Member · Accepted solution]

put the isnull before the isnumeric

isnull MUST ALWAYS be first

[Former Member · Accepted solution]

Ok, I changed that around and now I havea new error. Here is the formula so far:

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

{TranLink.TotValue}/ CCur({ProdInterim.F18Val})*100

It says that the word "then" is missing and puts the cursor after the ({ProdInterim.F18Val}) and before the 0.

[Former Member · Accepted solution]

shouldn't there be something between the field and the 0?

Like <> 0 ?

[Former Member · Accepted solution]

Try it like this:

if not isnull({ProdInterim.F18Val}) and isnumeric({ProdInterim.F18Val}) and {ProdInterim.F18Val}  = 0  then
({TranLink.TotValue}/ CCur({ProdInterim.F18Val}))*100

I'm assuming the order of precedence is ({TranLink.TotValue} / CCur({ProdInterim.F18Val})) * 100

If you wanted to * 100 first, change it to: {TranLink.TotValue} / (CCur({ProdInterim.F18Val})*100)

Hope this helps,

Jason

[Former Member · Accepted solution]

What if the vlaue in F18 is greater than 0?Can I do a greaterthan or equal to 0? What it it is less than 0? Can i do a lessthan or equal to 0 as well? Or is the 0 just like an all-encompasing numeric value?

[Former Member · Accepted solution]

Robert,

Looking at the formula again, you'll probably want to change it to not equal to 0.

if not isnull({ProdInterim.F18Val}) and isnumeric({ProdInterim.F18Val}) and {ProdInterim.F18Val}  != 0  then
({TranLink.TotValue}/ CCur({ProdInterim.F18Val}))*100

Otherwise you'll run the risk of getting a divide by zero error.

Jason

[Former Member · Accepted solution]

Again, what if the field is supposed to have a negative number? Does the != 0 just mean not 0?

Here is what I have:

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

({TranLink.TotValue}/ CCur({ProdInterim.F18Val}))*100

I check it in Crystal Reports and it gives me the error "A number, currency amount, boolean, or string is expected here." and it puts the cursor right before isnumeric.

As always, any help is greatly appreciated!!!!!

[Former Member · Accepted solution]

Actually I just revised it again. Here is what I have now:

if not isnull({ProdInterim.F18Val}) and {ProdInterim.F18Val}  < > 0  and isnumeric({ProdInterim.F18Val})then
({TranLink.TotValue}/ CCur({ProdInterim.F18Val}))*100

There is a less than < and greater than > sign right before the 0 but for some reason it is not showing up in the forum post.

I am still getting the number error and the cursor is still being placed before the isnumeric.

I may have neglected to mention this before but I am running Crystal Reports version 4.5. I know it's super old but it is all we have to work with.

[Former Member · Accepted solution]

The "!=" needs to be replaced with the opposing angle brackets. The angle brackets don't show up on the forums, so I borrowed the != from SQL Server. It doesn't work in CR. Sorry, I should have mentioned that before.

Any way, you need to make sure you are not trying to divide by 0.

Jason

[Former Member · Accepted solution]

Thanks haha. I figured that. I got that part down, read the post above for my current problem.

Thanks for the hlep!!!!!

[Former Member · Accepted solution]

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

[former_member260594]

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

{TranLink.TotValue}/ CCur({ProdInterim.F26Val})

[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

{TranLink.TotValue}/ CCur({ProdInterim.F18Val})

Any further help would be greatly appreciated!

[steve_norton]

Hi Robert,

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

Cheers,

Steve