carriage returns 1

[wolverinec7]

I've just started working on a new database, and they store their customer address field as a string rather than a memo and all in one field rather than address lines. because of that when on the db a user enters a carriage return in the string field it enters a ;

any ideas how to strip it out and make it look like a proper address field?

i.e.

abc ltd;123 london rd;london;L11AB

as
abc ltd
123 london rd
london
L11AB

 

[GJParker]

use the replace function e.g.

Replace(MyString, ';', ' ')

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[wolverinec7]

thats great gary thanks, how do i now make it line up like an address (its to be posted out)

after the replace, if i cut the width off at 'rd'looks like

abc ltd 123 london rd
london L11AB

i want it to move to a new line after each space

 

[GJParker]

To add in a carriage return use

Replace(MyString, ';', chr(10))


Gary Parker
MIS Data Analyst
Manchester, England

 

[wolverinec7]

superb, thanks for your help

 

[wolverinec7]

Gary im hoping you can help again, have got the report working perfectly, as long as the records fit on one page. im trying to get the report to print on a preprinted statement paper, and it will roughly work if i have 35 records per page, however if i say new page after recordnumber> 35 it gives every record after that its own page.

also ive got subreport in the page footer (to fit in pre printed boxes) for the totals, is there anyway i can supress unless its on the last page?

thanks in advance

Carl

 

[wolverinec7]

ignore the first part, as soon as i posted this i figured out how to do it (new page after recordnumber in[35, 70]) etc

still struggling on the last part though, SR has to be in the page footer in case the last pages is only half full (printed boxes at bottom of the page)

 

[GJParker]

a better formula for the first part so taht you won't be caught out with any number of records would be

RecordNumber Mod 35 = 0

For the second part you can add a suppression formula to the page footer section so that it only shows on the last page

pagenumber <> totalpagecount

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[wolverinec7]

as soon as i'd posted that last part i found the total page count and figured it out from there, but once again thanks for your help. the rework of the first formula takes out the fear that a super huge account may come along one day and messes it up.

thanks again

Carl

 

[wolverinec7]

hi gary im hoping you can help me on this again, despite your formula Replace(MyString, ';', chr(10)) working great for a while now the powers that be decide they dont like the spaces between the address and the postcode (which is a totally serperate field.

is there anyway i can modify it to remove the spaces, for example this is how one of the address fields imports

41 43 ANY STREET;ANYWHERE;;

where as some are

41 43 ANY STREET;ANYTOWN; ANYCOUNTY; ANYWHERE

the bottom workds great with the PC field after it but the first one has 2 lines of space between anywhere and the postcode - any helps greatly appreciated

rgs

Carl

 

[lbass]

Try Changing the formula to:

stringvar x := replace(right({table.string},2),";","");
replace(x,";",chr(10))

This assumes that only the last two characters might be semicolons.

-LB

 

[wolverinec7]

unfortunatly this doesnt work, it brings back the last 2 charachters of the address line so in the case where there is none it brings blanks and in the case where its full it returns 'ER' where the last line was manchester

 

[GJParker]

The formula below will work for the 2 examples you have provided

Local stringvar array x := split({table.address},';');
local stringvar y;
local numbervar i;

for i := 1 to ubound(x)
Do(
    if x[i] <> '' then
        y := y & LTrim(x[i]) & chr(10));

y & {table.postcode}

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[wolverinec7]

outstanding as always gary, cheers it works a treat

Carl

 

[lbass]

Sorry, my formula should have been

stringvar x := left({table.string},len({table.string})-2)+replace(right({table.string},2),";","");
replace(x,";",chr(10))

Should have tested it.

-LB