Results 1 to 13 of 13
  1. #1
    tomcoll is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    12

    Eliminating blank lines when printing labels


    I'm printing 6 line labels. The labels report normally will skip a line if there is no data. However, if I have an IIf statement, and here is no data, it will leave a blank line. I wish to "squeeze" up the blank lines so that there are no gaps betweed lines. My IIf statement tests for the presence of a field for labeling purposes. For example, if a Mobile Phone, I will print "Mobile:" followed by the Phone Number. I have a possibility of 4 different phone lines. Can I include some syntax into my IIf statement to make it work?? Thanks in advance for any help. Tomc

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Show the IIf expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tomcoll is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    12
    Thanks for responding, but how do I show the IIf? I went to properties for the IIf statement and didn't see that parameter???

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    IIf is not a property it is a function. So you have not created an IIf expression in a query or textbox ControlSource?

    I don't know what you mean by 'skip a line'. How does that happen? Do you want to provide the project or at least an extract of the relevant objects?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tomcoll is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    12
    OK. I'll try to be more specific. I have 6 lines in my label. Let's say I always have data for lines 1 through 4. I may or may not have a 5th line which contains a phone number. I code an IIf statement in an unbound text box within the label definition for line 5 so that I wouldn't print the title "Phone" if there were no phone reported. It looks something like this:
    IIf(IsNull([phone]),"","Phone:") and the phone field follows on same line. I have an Email field for line 6. So If I have no phone, I want the email line to print as line 5 and presently it is leaving a space for line 5. Sorry for not being more clear before. Thanks. Tomc

  6. #6
    tomcoll is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    12
    PS, this label format that I am creating will not be printed on an Avery label, but just printed as a report.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is each 'line' from a field in an address record? The only way I know to eliminate the blank due to no value is to concatenate the fields into a single string with embedded code for carriage return and line feed. This would be a very long expression in a query field so I would write a function that would take the 6 fields of record as arguments and the function would be called in query field. The function code would not concatenate any field that does not have data.

    Why is the blank line an issue?
    Last edited by June7; 11-15-2011 at 11:44 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    tomcoll is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    12
    Wow! Looks like I'm in over my head. I haven't got the foggiest on doing what you suggested. I simplified my example for you. Instead of having one possible blank line, I could have two or three and my issue is leaving several lines in my report which makes the appearance quite tacky. I just thought since the regular llabel program automatically "compacts" blank lines, I thought there may be some way of using the label program to do the same thing for me even if I'm using an IIf statement. If you are saying that I can't do this without doing the procedure you suggested, then I guess I can't do it.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What label program? I thought this was an Access report. Are the 4 possible phones in 4 fields. If so, try:

    Nz(phone1, Nz(phone2, Nz(phone3, Nz(phone4))))

    This will return either a phone or Null. If the label program rejects Nulls then maybe the blank line won't happen.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    tomcoll is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    12
    I'm trying to use the label report in Access. I'm sorry, but I don't know where to insert the NZ function?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am still confused. Access doesn't have a specialized 'label' report, it is just a report designed to print records in a label-like arrangement. Even the expression I offered will not eliminate a blank line in Access report due to an empty field. But if you want to try it out: use the expression to create a field in query. Refer to that constructed field in the report.

    Explain statement in your first post "The labels report normally will skip a line if there is no data." How is this possible?

    Do you want to attach project for analysis?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    tomcoll is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    12
    I have accomplished what I was trying to do with the label program in Access. I went to Google (searched on "Eliminating unwanted blanik lines in labels and reports") and it gave me the guide lines I was able to follow. Regarding my earlier statements about skipping a line, I meant that blank lines normally do not leave a space on the report using label programs. I'm using the label software in access to print a special report that will not be printed on Avery labels - just plain paper. I'm using that label feature because I wanted to print my records in three columns. Anyway, thanks for trying to help me. I hope this will help others that are trying to do the same thing that I'm doing. If there is an easier method of printing my data in three columns, I would like to hear it. Thanks again. Tomc

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, I see that tutorial http://office.microsoft.com/en-us/ac...001149472.aspx. It states that if the field contains null, even if it is the result of an expression, the CanShrink setting should still work. I tested and it does.

    I noticed that if the controls are touching, they act as if overlapping and the CanShrink won't work, as warned in the tutorial.

    The alternative is VBA code and also described in the tutorial.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Eliminating null lines in report
    By brobb56 in forum Forms
    Replies: 2
    Last Post: 09-26-2011, 11:05 AM
  2. Eliminating null lines in report
    By brobb56 in forum Reports
    Replies: 1
    Last Post: 09-26-2011, 11:01 AM
  3. Lines turning Blue when printing
    By hawkins in forum Access
    Replies: 1
    Last Post: 09-02-2011, 07:28 AM
  4. Report printing unknown blank lines.
    By dgrzalja in forum Reports
    Replies: 10
    Last Post: 11-02-2009, 12:21 PM
  5. Replies: 3
    Last Post: 10-23-2008, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums