Substracting Formula 4

[mnasuto]

Hi,

I have big problem with formula.

I have database field INFO which contains text:
SIC CODE=1234;Parent Country=UK; Turnover=£100000;

I need create report which must display:

SIC CODE 123
Parent Country UK
Turnover £100000

in different line. How can I do it?

Please help if you can.
Best,
Marta

 

[kenhamady]

Look up the Split() function, which might help you very simply.

If you are using an older version that doesn't have Split() you can use the InStr() to calculate where the ; positions are. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[mnasuto]

Hi Ken,

Thanks for quick reply.
I am using Crystal version 8.
Text field INFO has different length.
How I can find for example value for Parent Country?
I am not programmer.

Please give me some advice.

Best,
Marta

 

[kenhamady]

Is Parent Country always between the first and second ';' or is it always after the exact words "Parent Country="? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[MrBillSC]

Hi Marta,

You did not indicate if version 8 has the Split function. If it does, the following formula will work to extract the values from field INFO.

Split (Split ({INFO},";",3)[1],"=",2)[2]

This formula uses nested Split functions. The inner Split function: Split ({INFO},";",3) returns an array of 3 parts with a ";" as the delimiter. The [1] is a subscript for the first part of the three part array.

The outer split function:
Split (inner split function,"=",2)[2] takes the results of the inner split (the first element of the three part array which would be "sic code=1234&quot and splits it into a two part array with "=" as the delimiter, and returns the second part (subscript [2]) which is the value "1234".

You will need to set up a formula field for all three parts of your INFO field. The subscript of the inner Split function will change for each of the three parts.

Hope this helps.

 

[mnasuto]

It is always after the exact words "Parent Country=".
But sometimes in INFO it is not Present when value is emplty.
For Example:

SIC CODE=1234;Parent Country=UK; Turnover=£100000;

or for another record

SIC CODE=200;Turnover=£30000;

Best,
Marta

 

[dgillz]

Mr Bill,

Very cool. I have never thought of using split twice in the same expression. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[mnasuto]

This double split function is great, but what if I do not now how many values I have?
Example
SIC CODE=1234;Parent Country=UK; Turnover=£100000;

or for another record

SIC CODE=200;Turnover=£30000

Best,
Marta

 

[kenhamady]

The Split would work to answer your original question, and allow you to put all of the values on separate lines.

But, if you are trying to turn this one field into multiple fields, based on the descriptive label, that is a different issue. I would use the following:

WhileReadingRecords;
StringVar Inpt := {your.field};
StringVar LookFor := "Parent Country=" ; // or any other marker string
NumberVar Strt := InStr(Inpt,LookFor) ;
If Strt > 0
then Inpt [strt+Length(LookFor) to InStr(strt+Length(LookFor), Inpt,';') - 1 ]
else "" Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com