Link to home
Start Free TrialLog in
Avatar of IO_Dork
IO_DorkFlag for United States of America

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
SOLUTION
Avatar of Ido Millet
Ido Millet
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IO_Dork

ASKER

I figured that was the issue, just didn't know how to test for null or 0.  Does your suggestion work if {@Admin Comments} is null or nothing and not 0?
Avatar of Mike McCracken
Mike McCracken

Yes it will.

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;
);

Open in new window


mlmcc
Avatar of IO_Dork

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IO_Dork

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];
);
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
Avatar of IO_Dork

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.