Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Unanswered: Labels in Access 2007

    Again with my same non profit database, as part of general updates for it we are splitting up some name and middle initial combinations. Before this round of proposed changes, if you had a membership with 2 names on it (household/family) it would be entered into the "First Name" field like this "John & Jane" if there was a middle initial it would be "John Q.", and if there was a salutation it was also entered in the first name field "Mr. John Q." and last name was always a seperate field.

    One of the bigger changes im now making to the database at the request of our new membership director is to start splitting this information up. To Accomplish this, I've created several new columns, one for Salutation, one for First Name 2 and Middle Initial 2 for the second account holder in the household.

    The Problem comes in when it comes time to print labels. In word's mail merge i can tell it that if there is a value for "First Name 2" to insert an amerstand and space ahead of it, along with a space trailing it. and that so far works flawlessly for our letters
    .
    When it comes time to print the labels for the envelopes, its another story, The origonal labels used =Trim([First Name] & " " & [Last Name]), However when i modify them to show the "First Name 2" with an amperstand ahead of it, =Trim([First Name] & " " & [First Name 2] & " " & [Last Name]), I always get the amperstand even if there's no data in "First Name 2". Is there a way i can set this up to only show the amperstand if "First Name 2" is not a null value like how word is configured?

    thanks again for your help.
    -Zack

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by busdude View Post
    Again with my same non profit database, as part of general updates for it we are splitting up some name and middle initial combinations. Before this round of proposed changes, if you had a membership with 2 names on it (household/family) it would be entered into the "First Name" field like this "John & Jane" if there was a middle initial it would be "John Q.", and if there was a salutation it was also entered in the first name field "Mr. John Q." and last name was always a seperate field.

    One of the bigger changes im now making to the database at the request of our new membership director is to start splitting this information up. To Accomplish this, I've created several new columns, one for Salutation, one for First Name 2 and Middle Initial 2 for the second account holder in the household.

    The Problem comes in when it comes time to print labels. In word's mail merge i can tell it that if there is a value for "First Name 2" to insert an amerstand and space ahead of it, along with a space trailing it. and that so far works flawlessly for our letters
    .
    When it comes time to print the labels for the envelopes, its another story, The origonal labels used =Trim([First Name] & " " & [Last Name]), However when i modify them to show the "First Name 2" with an amperstand ahead of it, =Trim([First Name] & " " & [First Name 2] & " " & [Last Name]), I always get the amperstand even if there's no data in "First Name 2". Is there a way i can set this up to only show the amperstand if "First Name 2" is not a null value like how word is configured?

    thanks again for your help.
    -Zack
    How about something like this:

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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As this is a one off conversion issue I'd suggest several passes to gradually sort out the data

    you need to analyse your data to find out how big a task you have got. eg how many different title, forms of title do you have Mr and Mr.

    aim to do as much of the processing using update queries, and then tidy up the last few outriders manually

    it may be a good idea to do this as a form issuing a series of queries.

    it definitely will be a good idea to do a backup before changing anything.

    the SQL will be somehting like

    update mytable set title="Mrs" and forename=mid(PersonsName,4) where left(PersonsName,3)="Mrs"


    I'd suggest you do the work for the word mailmerge using a query
    use a function int he query to format the name correctly

    select BuildName(my,columns,from,db) as acolumn, my,column,list from Mytable


    create your function Buildname which returns a string used in the mailmerge

    your function should apply the business rules you need
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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