Results 1 to 5 of 5

Thread: Label Form

  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Question Unanswered: Label Form

    I'm the DB Admin/Membership director for a non-profit orginzation. I've recently updated the database with several new fields and tables to further our efforts (we used to have a "flat" database, which was starting to get messy, Now we have seperated the user data from the finanical information, and added a table to track mailings).

    Anyways, as part of the updates we did some work with our members names. Previously, in the database if you were a family, you were entered in the first name column as "John & Jane" with your last name in it's column. As part of the changes, we now have the following columns:
    [Salutation], [Last Name], [First Name], [First Name 2], [Middle Initial], [Middle Initial 2].

    The mail merge letters in word i can get to work properly, Using word's built in features to add the appropate spacing and Amperstand (e.g. Mr & Mrs John & Jane Doe)

    For the mailing labels i want something similar, Except without the salutation. However, since the First Name 2 field is empty on many of our members since it's a single membership, I cant hard code the amperstand into the label report. Currently our labels are printed out with the following:
    =Trim([First Name] & " " & [Last Name])

    I'm thinking i need to use the IIF statement to build the right syntax to only add the extra spacing and amperstand if there is a name present in the [First Name 2] field.

    =Iif(IsNull([First Name 2]),[First Name] &" "& [Last Name],[First Name] &" "&" "& [First Name 2] &" "& [Last Name])

    However, i cant seem to get it to work, any help/ideas?

  2. #2
    Join Date
    Dec 2009
    Posts
    8
    If i use the statement posted above it works except adding the Amperstand in the middle aka "John & Jane Doe" it shows up as "John Jane Doe"

  3. #3
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Use [First Name] & " & " & [First Name 2].

  4. #4
    Join Date
    Dec 2009
    Posts
    8
    Thank you very much, it worked.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    A simpler way is to use the fact that when you add anything to a Null value in Access, the results is Null. So

    [First Name] & " " & ("& " + [First Name 2]) & " " & [Last Name]

    will do the job without having to use IIF() to do the evaluation of [First Name 2].

    If [First Name 2] is Null, the ampersand which was "added" to it in ("& " + [First Name 2]) is also Null and will not show.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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