Truncate query 1

[AMS100]

Our address's are stored in the database as one field with each line of the address signified by a ¨€ (I think its the visual character for line feed in ASCI (I could also be talking rubbish but its something to-do with line feed)). Addresses are generally 4 lines. E.g. an address would look something like this within the DB
First Line¨€
Second Line¨€
Third Line¨€
Fourth Line¨€

I would like to display each line of the address as one field. Who would I get a formula field to (in the first instance) display all text before the first '¨€' then for the second line display all the text between the first '¨€' and the second '¨€' etc... ? I think this makes sense, thanks for any input.

 

[synapsevampire]

You've pasted in 2 characters (¨€), not one, so it's not a line feed. A line feed is chr(10)

To learn the value of a character in Crystal, use:

asc("¨€&quot

Now to omit this offending character, assuming that it's supposed to be only one character, use formulas on each field:

@firstline
left({table.field},len({table.field})-1)

@secondline
left({table.field2},len({table.field2})-1)

etc.

If it is 2 characters, change the 1 to a 2. You might alos have to ensure it's not null or less than 2 in length:

@firstline
if len({table.field}) > 1 and not(isnull({table.field})) then
left({table.field},len({table.field})-1)
else
""

etc.

Hope this resolves for you.

-k

 

[AMS100]

Thanks for the prompt reply. Unfortunately my initial query didn't make sense. For some reason when passing the question to the forum the ASCI char got replaced with ''€ it should be a black box (which I cannot create manually) For the remainder of this I shall use # instead of a 'black box'. Our database stores an address exactly as shown below:

ADDRTEXT
---------------------------------
FIRSTLINE#
SECOND LINE#
THIRD LINE#
FOURTH LINE

When the address is entered into the software the # is where the user has hit return. I need to split this one field into - upto 4 separate formula fields, I say 'upto' because an address could have just the one line. I think the post from Synapsevampire assumes I have 1 field for each address line, therefore, it’s removing the last char. Thanks very much

 

[AMS100]

Right, I'm getting somewhere with this. I'm now able to extract the first line of the address by using the following formula field: -

Left({Address.Addrtext}, Instr ({Address.Addrtext}, Chr(10))-3);

And I can retrieve the last line of the address by using the above formula and replacing the 'Left' with 'Right'. This is fine for 2 line addresses but becomes a problem when the address has 3 or more lines. Any ideas?

 

[Naith]

You can split your address up:

//{@Formula1}

WhilePrintingRecords;
StringVar Array Sp := [""];

Sp:= Split ({Address}, "#");
' ';

//{@Formula2}

WhilePrintingRecords;
StringVar Array Sp;

If UBound(Sp) > 1
Then Sp[2]

This'll give the 2nd line of the address. For the third, replace 1 with 2 and 2 with 3. And for the fourth - well, I won't insult your intelligence, I'm sure you get the idea.

If you were to continue with your current way of doing things, it'd look like this:

Mid(  {Address},
            Instr({Address},'#')+1,
            Instr({Address},'#')-1);

for the 2nd line, and

Mid(  {Address},
            InStr({Address},'#')+1,
            InStr({Address},'#')+1,
            InStr({Address},'#')-1);

for the 3rd...

Good luck with whichever approach you decide on.

Naith

 

[AMS100]

I've another (probably quite noddy) question about the above. It all works fine but it now turns out I need to remove the very last char on each line. How would I go about doing this, I’ve not really used arrays before so am a bit stumped. P.S I’m using the solution directly above this post.
Thanks, any help is much appreciated.

 

[synapsevampire]

Try:

left(Sp[2],len(Sp[2])-1)

Replacing 2 with the array element you're working with.

-k

 

[AMS100]

Thanks for that it nearly works perfectly, but...
It doesn't appear to work for anything other than the 2nd line? The code for the second line reads:

Whileprintingrecords;
StringVar Array SP;

If UBound(SP) >1
Then left(Sp[2],len(Sp[2])-1)

Works. and the 3rd line reads
Whileprintingrecords;
StringVar Array SP;

If UBound(SP) >2
Then left(Sp[3],len(Sp[3])-1)
doesn't work? Falls over with an error
'String length is less than 0 or not an integer'
I’m guessing this is because the 3rd line can be blank (and is in some of my records), how would I get around that?
Thanks again

 

[Naith]

Some of your later array records may be blank, but the whole point of putting in the Ubound function is to check if the array component you're about to work on contains any values, so that you don't get this error.

I suspect that this practice of subtracting one from the array may be contributing to your error. To be sure, you can remove the -1 from the Sp[3] formula and check for yourself.

Regarding the spurious character in the array, it's better to not include it in the first place, rather than repeatedly trim it out of the array once it's there.

In the formula where you're building your array, change this line:

Sp:= Split ({Address}, "#"

to this:

Sp:= Split ({Address}, "#X"

where #X represent the two characters you don't want.

Naith