Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move Empty fields in a report (2002 XP)

    I have a database which has a report which is esstially a letter fed through from a query. If one line of the persons address is empty how can i get the next field to take its place and not leave a gap? (similar to the do not leave blank lines feature in mail Merge). help threw up something about expression and IIf and then died before I could read it and won't reappear.

    Any ideas?

    Thanks in advance

    Michelle

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move Empty fields in a report (2002 XP)

    MSKB article How to Concatenate Fields in a Text Box to Remove Blank Lines describes a method to do this.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Empty fields in a report (2002 XP)

    Thanks for that Hans. Maybe its too early in the morning for me. But I cab't get my head around what it saying!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move Empty fields in a report (2002 XP)

    There are basically two ways to suppress empty lines in a report.

    The first is to place a series of text boxes bound to the various fields below each other, with no space in between but taking care that they don't overlap. Set the Can Shrink property of these text boxes to Yes. If you want the entire section to adjust its size too, also set the Can Shrink property of the section to Yes. See screenshot.

    The second is the method described in the MSKB article. Instead of using a separate text box for each field, use one that Can Grow and Can Shrink, Its control source is an expression (formula) that concatenates the fields, with Chr(13) & Chr(10) to create a line break. ASCII characters 13 (carriage return) and 10 (line feed) are what computers and printers use to go to a new line. Let's take a simple example: you have a ContactName and a CompanyName field. The latter is not always filled in, but if it is, it should be on a separate line below the ContactName. The expression

    =[ContactName] & Chr(13) and Chr(10) & [CompanyName]

    will always contain a line break, even if CompanyName is empty. To avoid this, you can use IIf:

    =[ContactName] & IIf(IsNull([CompanyName]),"",Chr(13) & Chr(10) & [CompanyName])

    If the company name is empty, only an empty string is appended to ContactName, otherwise, a line break followed by the company name. Another way to do this is

    =[ContactName] & (Chr(13)+Chr(10)+[CompanyName])
    Attached Images Attached Images
    • File Type: png x.png (4.3 KB, 0 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •