extracting data 2

[jvand]

Hello,

I work in a library and i am trying to make spine labels.
Our spine labels look like this:
A16.1
zh
ALBA
100010 (is barcode)

"A16.1 zh ALBA" is located in one field: LIB_ITEMS.CALL_NUMBER (String)
Is it possible to extract the data and make a spinelabel?

regards and thanks in advance
Joop Vanderheiden

 

[kenhamady]

What is the rule for identifying where the components start and stop? Are there a fixed number of components. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[jvand]

Hello,

In LIB_ITEMS.CALL_NUMBER (String) field can look like this
&quot;A16.1<1space>zh<4spaces>ALBA&quot; and &quot;B23.35<4spaces>JONG&quot;
The first component (in the examples A16.1 or B23.35) can be of any length
Then always one (1) space.
The second component (in the examples zh) isn't always in the field.
The third component (in the examples (ALBA or JONG) is always proceeded by four (4) spaces.

I hope this gives you enough information to help me.

Thanks.

Ragards
Joop Vanderheiden

 

[kenhamady]

If the second piece doesn't exist do end up with 5 consecutive spaces or something else?

Crystal has a feature that allows you to find the position of a shorter string within a longer string. The InStr() function. This will allow you to calculate where the first single space occurs and where the first set of 4 spaces occurs.

You can use these position numbers to extract the appropriate pieces (using substring) into three different formula fields, which you can stack on your report.

You may also need to do some if-then-else logic to catch the second piece being missing and adjust for that. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

You could also put it one formula, as long as you format the field to print on multiple lines (Can Grow check box). This sample formula will have either 2 or 3 lines. You could also add the bar code to this.
I'm a big fan of libraries, so here is small donation of time
StringVar LastLine ;
StringVar FirstLine ;
StringVar MiddleLine ;
{LIB_ITEMS.CALL_NUMBER}
FirstLine := {LIB_ITEMS.CALL_NUMBER} [1 to InStr({LIB_ITEMS.CALL_NUMBER},' ')-1] + CHR(13) ;
If InStr({LIB_ITEMS.CALL_NUMBER},' ') = InStr({LIB_ITEMS.CALL_NUMBER},' ') then
MiddleLine := ''
Else
MiddleLine := {LIB_ITEMS.CALL_NUMBER} [InStr({LIB_ITEMS.CALL_NUMBER},' ')+1 to InStr({LIB_ITEMS.CALL_NUMBER},' ')-1] + CHR(13) ;
LastLine := {LIB_ITEMS.CALL_NUMBER} [InStr(SpineLabel,' ')+4 to Length({LIB_ITEMS.CALL_NUMBER})] ;
FirstLine + MiddleLine + LastLine
Malcolm
wynden@telus.net