Crystal 8.5

[CEG]

I am trying to use the formula Split on an Address field.
In the database the whole address is held in one field, using the Manual Line Break, to separate the lines.Hence the following formula to provide 3 address lines.

Address1:
split ({SL_Account.Cuadress],chr(13),1)
Address2:
split ({SL_Account.Cuadress],chr(13),2)
Address3:
split ({SL_Account.Cuadress],chr(13),3)

Message appears 'A String is required here' highlighting the figure 1, 2, or 3.

Any advice will be greatly appreciated.

Thanking you in anticipation.

CEG

 

[SmpTraining]

I don't think you are using the split function in the correct way

Use code such as:

StringVar FullAddress:= "Line1, Line2, Line3,";
StringVar Array AddressLine:=Split (FullAddress, ',');
AddressLine[2];

to extract each address line.

Hope that helps. Steve Phillips, Crystal Consultant

 

[rosemaryl]

Dear EG,

Well, you have got the operation of the count wrong. The count portion of the expression doesn't return the string from the position in the "array". The counter tells Crystal what number strings to split the array into using the delimeter (count the delimiters).

A -1 or no value for the counter, splits the string using all delimeters. In your case, -1 or no value will split your string into 3 pieces delimited by the Chr(13)!

Also, I assume a typo when transcribing here, but the ending field bracket shouldn't be ] it should be } so that would return an error.

"split ({SL_Account.Cuadress],chr(13),1)"


Since I don't know the point of your formula it is hard to know what to advise you... but here is an example using your corrected formula to assign each piece of the address to a variable.

//begin formula
stringvar array add;
stringvar add1;
stringvar add2;
stringvar add3;

add := split ({SL_Account.Cuadress},Chr (13),-1);

add1 := add[1] ;
add2 := add[2] ;
add3 := add[3];

add1
//a formula cannot result in an array
//so I chose add1 to return

The variables really aren't necessary because you could just do:

//
stringvar array add;
add := split ({SL_Account.Cuadress},Chr (13),-1);

add[1]
//

Hope that helps.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CEG]

Thanks Rosemary,

I can see the plot. The purpose of the split is that in the address data if used for mailing labels etc. there are a lot of blank space, if I could split this and put them in as lines with suppress if blank etc.

I opted for the first formula which said No errors found, but when I tried to run the report Message displayed A subscript must be between 1 and the size of the array. There is only one option ok.

I have spent a lot of time on this formula and I would really appreciate help to crack it.

Thanking you in anticipation.

CEG

 

[SmpTraining]

If an array contains 3 elements, any attempt to reference the 4th will result in the error you mentioned.

To get around this, change all your address line references from:

@AddressLine2

StringVar FullAddress:= <your_address_field>;
StringVar Array AddressLine:=Split (FullAddress, chr(13));
AddressLine[2];

To:
@AddressLine2

StringVar FullAddress:=<your_address_field>;
StringVar Array AddressLine:=Split (FullAddress, chr(13));
if UBound (AddressLine) >= 2 then 
   AddressLine[2];

else
""

UBound(array) returns the number of array elements in the array. Steve Phillips, Crystal Consultant

 

[rosemaryl]

Dear CEG,

Well that error indicates that some of the strings are null.

You should be able to change it thus:

//begin formula
stringvar array add;
stringvar add1;
stringvar add2;
stringvar add3;

add := if isnull({SL_Account.Cuadress}) then [" " +Chr(13), " "+Chr(13)," "]
else if {SL_Account.Cuadress} ="" then [" " +Chr(13), " "+Chr(13)," "]
else
split ({SL_Account.Cuadress},Chr (13),-1);

add1 := add[1] ;
add2 := add[2] ;
add3 := add[3];

add1
//end

I tested this on a report of mine by substituting a formula for your field name and tested it with values and with "" and added the isnull, just to cover all bases.

The formula ran without error.

Let me know if we need to fine tune it further.

regars,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CEG]

Have tried the new formulae but still come up with the same message
A subscript must be between 1 and the array.

Appreciate your help

CEG

 

[rosemaryl]

Dear CEG,

Well, the problem then would be add[1], if the array is null then there wont be anything there.

so modify the bottom part (add[1]) as follows:

//begin formula
stringvar array add;
stringvar add1;
stringvar add2;
stringvar add3;

add := if isnull({SL_Account.Cuadress}) then [" " +Chr(13), " "+Chr(13)," "]
else if {SL_Account.Cuadress} ="" then [" " +Chr(13), " "+Chr(13)," "]
else
split ({SL_Account.Cuadress},Chr (13),-1);

add1 := if ubound(add) >= 1 then add[1] else "" ;
add2 := if ubound(add) >= 2 then add[2] else "";
add3 := if ubound(add) = 3 then add[3] else "";

//end

Sorry we are having to do so much back and forth.

We should probably be able to really simplify, but I am at a client and don't have time right now... if I get a chance I will work on it over the weekend to see if I can make more elegant. Hoping this one is the charm for you.

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CEG]

Thanks for your help guys. It works great!!!

Will just have to get my head around all the logic in the formulae.

CEG