on 09-12-2008 3:16 PM
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!
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!!!!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
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!!!!!
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.
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})
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
Hi Robert,
Since your question is specifically about CR formulas, I've moved it into the CR design forum.
Cheers,
Steve
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.