Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Label Report (w/Conditional Field Separators) (97)

    Probably been asked a million times! In creating a mailing label report, how do i insert a character (comma) after a field, only if it is not empty, and leave it out if it is empty?

    [lastname], [credentials], [title]

    The commas should only show up if there is data in the credentials or title field.... so the commas are conditional... and therefore cannot be placed in the mailing label as text... Any assistance, is greatly appreciated. Jenn.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Report (w/Conditional Field Separators) (97)

    Try this:
    [lastname] & IIf(Len([credentials]) = 0, " ", ", ") & [credentials] & IIf(Len([title]) = 0, " ", ", ") + [title]
    Francois

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Twickenham, UK
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Report (w/Conditional Field Separators) (97)

    I've always used 1 of the 2 solutions outlined below.

    Option 1
    Create 2 unbound fields. Place the first one between the lastname and credentials field and set its contol source property to:
    =Iif(isnull([credentials])=true,"",", ")

    The second unbound field goes between credentials and title, with the control source set to:
    =Iif(isnull([title])=true,""," ,")

    Getting the spacing exactly righy on this is sometimes painful, so the other alternative is

    Option 2
    Create 1 unbound field to include all three fields and the comma's. It's control source would look some thing like:
    =[lastname] & Iif(isnull([credentials])=true,"",", ") & Iif(isnull([credentials])=true,"",[credentials]) & Iif(isnull([title])=true,""," ,") & Iif(isnull([title])=true,"",[title])

    This assumes that there is always a lastname

    HTH (and is correct - I'm sure someone will point out if there's a better way)

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Report (w/Conditional Field Separators) (97)

    Thanks Francois and Chris - I used a combination of your two suggestions and my comma problem is a thing of the past! Was one of those things that always bugged me! I am appreciative of the time you took to answer me. Thanks again! Oh, the actual control source i used was as follows:

    =Trim([Prefix] & " " & [FirstName] & " " & [LastName] & IIf(IsNull([Credentials])=True,"",", ") & [Credentials] & IIf(IsNull([Title])=True,"",", ") & [Title])

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Report (w/Conditional Field Separators) (97)

    You can do it even easier by using the built in null conditions:

    =Trim(([Prefix] + " ") & [FirstName] & " " & [LastName] & (", " + [Credentials]) & (", " + [Title])

    if [Credentials] or [Title] are null then the section within the particular parenthesis is null and the comma is omitted. The same is true if the [Prefix] is null - the leading space is omitted.

Posting Permissions

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