Formula Help

[chrisppppp]

Crystal XI

Hi, i'm using the formula below to work out the surname of a string:

{@Surname}
stringvar array x := split({tblPolicy.PolicyHolder}," ");
x[ubound(x)]

However whilst it is running i get the following error:

"A subscript must be between 1 and the size of the array"

and it highlights the 'x[ubound(x)]' part of the formula


Can anybody give me any help or suggestions for what i should do with this so that my report runs correctly?

Cheers

 

[IanWaterman]

Looks like it has encountered a null. Try adding a test for null.

if not isnull({tblPolicy.PolicyHolder} then
stringvar array x := split({tblPolicy.PolicyHolder}," ")
else x:=1
;
x[ubound(x)]

Ian

 

[chrisppppp]

I get an error saying that a "string array is required" when x:=1?

What should i change this to?

 

[IanWaterman]

Can you show whole formula so we can see how you use x

Ian

 

[chrisppppp]

How do you mean?

I use the {@Surname} formula as above, and then in another formula i see if {@Surname} is equal to another string form another data source to see if the match.

 

[IanWaterman]

Are you getting that new error in @Surname or the other formula. Surname should execute OK now with the null test.

Need to see follow on formula.

Ian

 

[chrisppppp]

i'm getting that error from the formula you provided when i click on the "check" button in formula editor. It highlights the x:=1 after the else

 

[IanWaterman]

Sorry misread you first email.

X is an array

The value in [] must be a number

eg x[y]

Try

stringvar array x := split({tblPolicy.PolicyHolder}," ");

if isnull({tblPolicy.PolicyHolder} then
numbervar y := 1 else y:= ubound(x)];

x[y]


Ian

 

[lbass]

Try:

//{@Surname}:
stringvar array x;
if not isnull({tblPolicy.PolicyHolder}) then
x := split({tblPolicy.PolicyHolder}," ");
if ubound(x) > 0 then
x[ubound(x)]

-LB

 

[chrisppppp]

Sorry been a long time, but just came back to this. Ibass your formula comes back with "The word Else is missing". Where should it be placed in your formula? After the x := split({tblPolicy.PolicyHolder}," "); ??

Cheers

 

[IanWaterman]

Sounds like you have missed aa ; somewhere.

Probably in this line

x := split({tblPolicy.PolicyHolder}," ");

LBs formula does not require an else.

Ian

 

[chrisppppp]

nah the ; is there. still get that error tho. any ideas?

By the way Ian, i tried your formula and i still get the error half way through running "A subscript must be between 1 and the size of the array" it highlights the x[y] part of the formula.

 

[IanWaterman]

LBs solution is better than mine so need to get his working.

Please paste your formula so we can see what might be causing error.

Ian

 

[chrisppppp]

stringvar array x;
if not isnull({tblPolicy.PolicyHolder}) then
x := split({tblPolicy.PolicyHolder}," ");
if ubound(x) > 0 then
x[ubound(x)];

this is exactly how i've got it in my report. And it brings up the error that it needs an "else" somewhere.

 

[IanWaterman]

Looks like your ubound(x) is returning 0 and it does not know what to do.

That would imply you split has not found a " ".

What do you want it to do, alternatively it has come across a field with just " " or "" which is not the same as a Null.

In File -> Report Options
Have you got set Nulls to default checked?

That would render first part of formula useless as there will not be any Nulls.

Ian

 

[chrisppppp]

i haven't got the nulls to default checked.

What i ultimately want to do is, unfortunuately the database i am working on holds the full name as just one string rather than split out the main parts of the name ie, initial, first name, middle name, surname. What i wanted to do is split out the surname, so that is why i've used the surname formula as above. The other problem is that in the PolicyHolder field there isn't just names set out in the normal way, there is also say one word names with no spaces, so i don't know if that is maybe causing an issue?

 

[IanWaterman]

Try

stringvar array x;
if not isnull({tblPolicy.PolicyHolder}) then
x := split({tblPolicy.PolicyHolder}," ");
if ubound(x) > 0 then
x[ubound(x)]
else {tblPolicy.PolicyHolder};

Ian

 

[chrisppppp]

I still get the same "Else" error with it pointing to the x := split({tblPolicy.PolicyHolder}," "); part of the formula

 

[IanWaterman]

I have modified your formula to use a simple string with and without a space.

stringvar array x;

x := split("tblPolicyPolicyHolder"," ");
if ubound(x) > 0 then
x[ubound(x)]

Returns tblPolicyPolicyHolder

and

stringvar array x;

x := split("tblPolicy PolicyHolder"," ");
if ubound(x) > 0 then
x[ubound(x)]

returns

PolicyHolder

There must be record with strange data in that field causing Formula to fail.

You need to do some investigation by narrowing down your data set to identify the faulty record.

Ian

 

[lbass]

Or add an 'else' and maybe a check for the space:

stringvar array x;
if not isnull({tblPolicy.PolicyHolder})and
instr({tblPolicy.PolicyHolder}," ") <> 0 then
x := split({tblPolicy.PolicyHolder}," ") else
x := {tblPolicy.PolicyHolder};
if ubound(x) > 0 then
x[ubound(x)];

-LB