[RBG7-L] - Re: Suppressing blank lines in mailing labels

[David M. Blocker]

Thanks for this Karen - you never know when it will come in handy!   David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: [EMAIL PROTECTED] To: RBG7-L Mailing List Sent: Monday, August 23, 2004 11:43 AM Subject: [RBG7-L] - Re: Suppressing blank lines in mailing labels John: I really miss the 6.5++ "line compression" in labels.  I would often create a report as a 8 1/2 x 11" label to be able to easily do this. Although a stored procedure and ifnull/ifexists is easy enough for an address label (because you have at most 3 or 4 lines), what would you do if you had 12?   I'm doing a list for my high school reunion where every student will have a name, but then any of the next 12 data items could be blank.  Could you imagine the "if" construction for that?    I decided to create a temp table with the name field, then 12 columns named Line1, Line2, etc. with a NOTE datatype.  My program steps through each piece of data and fills in the "next" column of data.  I'll post my code below in case anyone would ever find this useful. Karen -- code is stripped of usual beginning and ending cleanup, initialization, etc. DECLARE C1 CURSOR FOR SELECT +  StudentID,Fullname,MarriedName,Address,email,+  Hobbies,Occupation,GrandChildren, <etc., etc.> +  FROM students OPEN c1 WHILE 1 = 1 THEN  FETCH C1 INTO vid, vfullname, vmarriedname, vaddress, vemail, +    vhobbies, voccupation, vgrand, <etc.>  IF SQLCODE = 100 THEN    BREAK  ENDIF  SET VAR vloop INTEGER = 2  INSERT INTO tmpbooklet (studentid, line1) VALUES .vid, .vfullname  IF vaddress IS NOT NULL THEN    SET VAR vcolumn = ('Line' + CTXT(.vloop) )    UPDATE tmpBooklet SET &vcolumn = .vaddress WHERE studentid = .vid    SET VAR vLoop = (.vLoop + 1)  ENDIF  IF vemail IS NOT NULL THEN    SET VAR vcolumn = ('Line' + CTXT(.vloop) )    UPDATE tmpBooklet SET &vcolumn = .vemail WHERE studentid = .vid    SET VAR vLoop = (.vLoop + 1)  ENDIF  IF voccupation IS NOT NULL THEN    SET VAR vcolumn = ('Line' + CTXT(.vloop) )    UPDATE tmpBooklet SET &vcolumn = .voccupation WHERE studentid = .vid    SET VAR vLoop = (.vLoop + 1)  ENDIF  -- YOU GET THE IDEA! ENDWHILE > Thanks Razzak, I am familiar with the process of > using IF statements to > filter out the blank lines in reports and labels. > What I meant to ask is.... > Does version 7 label designer have a checkbox (like > 6.5++) that will > automatically suppress blank lines?   John, what I generally end up doing is creating a stored procedure: FormatAddress(pAddr1, pAddr2, pCity, pState, pZip) RETURN NOTE or whatever arguments are appropriate for the project I'm working on.  I put my IF logic in there, and call the procedure whenever I need an address.  I use the procedure in labels (naturally), but also forms and reports where I want to show the mailing address. The advantage is that if I decide to change my address formatting logic, I only do it in one place.