IO_Dork
asked on
StringVar Error: start position is less than 1 or not an integer
I have a formula field called "Billing" that uses Local StringVar. When I run the report I get the error:
"Start position is less than 1 or not an integer" and then it kicks me into the formula to debug it.
How do I fix the formula field to deal with this?
Formula field {@Billing}:
Local StringVar strBill;
strBill := Mid({@Admin Comments},InStrRev({@Admin Comments},"$bill"));
If Length(strBill) > 20 then
strBill := "*****" & strBill;
strBill
"Start position is less than 1 or not an integer" and then it kicks me into the formula to debug it.
How do I fix the formula field to deal with this?
Formula field {@Billing}:
Local StringVar strBill;
strBill := Mid({@Admin Comments},InStrRev({@Admin
If Length(strBill) > 20 then
strBill := "*****" & strBill;
strBill
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes it will.
I believe this will be slightly faster
mlmcc
I believe this will be slightly faster
Local StringVar strBill;
if InStrRev({@Admin Comments},"$bill") > 0 then
(
strBill := Mid({@Admin Comments},InStrRev({@Admin Comments},"$bill"));
If Length(strBill) > 20 then
strBill := "*****" & strBill;
);
mlmcc
ASKER
that helps with the error, but I still have an issue with this formula...its not returning the text that its looking for. For instance if the field contains the following:
Setup for recurring annual bill of $50.00
$bill51 07-03-19
The formula field should return "$bill51"
Conditions:
- the "billing string" could be as long as 11 characters or more (ex. $bill52.2.1)
- the billing string could be at begining, middle, or end of the field's entire string
- the billing string could be followed by a space, return, or nothing at all if its at the end of the field's entire string.
- the billing string could be mixed case, all caps, or lower case (i have a feeling this does not matter).
Secondingly, I don't what purpose the last part of the formula serves:
If Length(strBill) > 20 then
strBill := "*****" & strBill;
);
in fact, in some cases the formua things the strBill is longer than 20 and returns for example "*****$bill51" instead of just "$bill51"
Also, to be clear the formula field {@Admin Comments} was created to pull the contents of a memo field since CR does not allow you to work directly with a memo field.
Setup for recurring annual bill of $50.00
$bill51 07-03-19
The formula field should return "$bill51"
Conditions:
- the "billing string" could be as long as 11 characters or more (ex. $bill52.2.1)
- the billing string could be at begining, middle, or end of the field's entire string
- the billing string could be followed by a space, return, or nothing at all if its at the end of the field's entire string.
- the billing string could be mixed case, all caps, or lower case (i have a feeling this does not matter).
Secondingly, I don't what purpose the last part of the formula serves:
If Length(strBill) > 20 then
strBill := "*****" & strBill;
);
in fact, in some cases the formua things the strBill is longer than 20 and returns for example "*****$bill51" instead of just "$bill51"
Also, to be clear the formula field {@Admin Comments} was created to pull the contents of a memo field since CR does not allow you to work directly with a memo field.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For my record keeping I removed the following b/c it seemed to serve no purpose:
If Length(strBill) > 20 then
strBill := "*****" & strBill;
THE FINAL FORMULA USED:
Local StringVar strBill;
if InStrRev({@Admin Comments},"$bill") > 0 then
(
strBill := Mid({@Admin Comments},InStrRev({@Admin Comments},"$bill"));
strBill := Split(strBill,' ')[1];
);
If Length(strBill) > 20 then
strBill := "*****" & strBill;
THE FINAL FORMULA USED:
Local StringVar strBill;
if InStrRev({@Admin Comments},"$bill") > 0 then
(
strBill := Mid({@Admin Comments},InStrRev({@Admin
strBill := Split(strBill,' ')[1];
);
You probably should have considered splitting the points. Ido answered the question on what was wrong. James provided a formula that would answer the initial question.
My comments were intended to show what I thought was an improved formula (based on James). My final comment answered your second question..
If you wish I can reopen the question to allow you to accept multiple comments.
mlmcc
My comments were intended to show what I thought was an improved formula (based on James). My final comment answered your second question..
If you wish I can reopen the question to allow you to accept multiple comments.
mlmcc
ASKER
Yes, please reopen the question.
I did not intend to leave anyone out as I thought Jame's solution was from you (my bad I should have paid better attention when assigning points). However, I posted the question in search of a solution, not a an explaination of what was wrong...I guess I should be more clear with the questions I post. Don't get me wrong, I appreciate Ido's input, its just that from my point of view I did not know how to implement his suggestion so it did not provide me with a final solution. But, I see you point and I am happy to split points amoung everyone in order to be equitable and fair. Please reopen.
Thanks.
I did not intend to leave anyone out as I thought Jame's solution was from you (my bad I should have paid better attention when assigning points). However, I posted the question in search of a solution, not a an explaination of what was wrong...I guess I should be more clear with the questions I post. Don't get me wrong, I appreciate Ido's input, its just that from my point of view I did not know how to implement his suggestion so it did not provide me with a final solution. But, I see you point and I am happy to split points amoung everyone in order to be equitable and fair. Please reopen.
Thanks.
ASKER