At 05:30 PM 3/15/2015, Michael Byerley wrote:
Code I use in Stored Procedure:{begin code} *(sp_Addr mod 011505 mbyerley) *(Use only 2 Ret VarNames vRetText and vRetInt) *(Input Params should be of: pt1,pt2, for text pi1,pi2 for Int) CLEAR VAR MICRORIM_RETURN, stp_return SET VAR pt1 TEXT SET VAR vcr = ((CHAR(13)) + (CHAR(10))) SELECT mainname, address1, address2, city, statename, zipcode, zipplus + INTO vspmainname IND vspin0, vrettextess1 IND vspin0, vrettextess2 IND vspin0, + vspcity IND vspin0, vspstatename IND vspin0,vspzipcode IND vspin0, + vspzipplus IND vspin0 + FROM vwcontacts WHERE (cid = .pt1) IF SQLCODE = 0 THEN SET VAR vrettext = .vspmainname IF vrettextess1 <> NULL OR (SLEN((STRIM(.vrettextess1)))) > 0 THEN SET VAR vrettext = (.vrettext + .vcr + .vrettextess1) ENDIF IF vrettextess2 <> NULL OR (SLEN((STRIM(.vrettextess2)))) > 0 THEN SET VAR vrettext = (.vrettext + .vcr + .vrettextess2) ENDIF IF vspcity <> NULL OR (SLEN((STRIM(.vspcity)))) > 0 THEN SET VAR vrettext = (.vrettext + .vcr + .vspcity) ENDIF IF vspstatename <> NULL OR (SLEN((STRIM(.vspstatename)))) > 0 THEN SET VAR vrettext = (.vrettext + ' ' + .vspstatename) ENDIF IF vspzipcode <> NULL OR (SLEN((STRIM(.vspzipcode)))) > 0 THEN SET VAR vrettext = (.vrettext + ' ' + .vspzipcode) ENDIF IF vspzipplus <> NULL OR (SLEN((STRIM(.vspzipplus)))) > 0 THEN SET VAR vrettext = (.vrettext + '-' + .vspzipplus) ENDIF ELSE SET VAR vrettext = 'No Address Entry' ENDIF Clear Var pt1 CLEAR VAR vspin0,vspcity,vspstatename,vrettextess1,vrettextess2, + vspmainname,vspzipcode,vspzipplus RETURN .vrettext {end code}
Mike, This is an excellent code as well as a swift approach. However, if all these lines of addresses, filled and NULL, could be concatenated as one expression with a data type, like NOTE. Then, using the "Variable Memo" control on report, with appropriate width and height placed on appropriate report band, you can eliminate any BLANK (NULL) values using the [ ] Suppress Empty Lines option of the Variable Memo properties. When multi-line data is displayed, the Suppress Empty Lines property will remove empty lines. Example: Report Expression: Variable Name: CompanyAddress Expression: (CompanyName+(CHAR(013))+AddressLine1+(CHAR(013))+AddressLIne2+ + AddressLine3+(CHAR(013))+(City+','&State&ZipCode)) Calculate On: PH Variable Memo Properties: [x] Stretch [x] Suppress Empty Lines This is all-in-one solution and works perfectly! Very Best R:egards, Razzak. www.rbase.com www.facebook.com/rbase -- 32 years of continuous innovation! 17 Years of R:BASE Technologies, Inc. making R:BASE what it is today! --
