Thread: Labels in Access 2007
12-06-09, 21:48 #1Registered User
- Join Date
- Dec 2009
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.
12-07-09, 03:10 #2Registered User
- Join Date
- Oct 2009
12-07-09, 05:23 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 needI'd rather be riding on the Tiger 800 or the Norton