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
IO_DorkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
You have to guard against cases where
InStrRev({@Admin Comments},"$bill")
doesn't find the target.
0
James0628Commented:
Ido's correct.  For example:

Local StringVar strBill;

if InStrRev({@Admin Comments},"$bill") = 0 then
  strBill := ""
else
  strBill := Mid({@Admin Comments},InStrRev({@Admin Comments},"$bill"));
 If Length(strBill) > 20 then
     strBill := "*****" & strBill;

 strBill
0
IO_DorkAuthor Commented:
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?
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

mlmccCommented:
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
0
IO_DorkAuthor Commented:
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.
0
mlmccCommented:
If there will always be a space after what you want try


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

Open in new window


mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IO_DorkAuthor Commented:
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];
);
0
mlmccCommented:
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
0
IO_DorkAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.