Mailing Labels with spouse or partners

[Judy]

How do I set up mailing labels in Access 2007 so that my spouse/partner
fields: FirstName, LastName, FirstName2, and LastName2 will print out as:
John Smith, John and Mary Smith, or John Smith and Mary Jones. (same
street/PO Box address)? The wizard doesn't seem to consider this an option,
and I don't know the syntax or programming code to accomplish this in design
view.

 

[Chuck]

How do I set up mailing labels in Access 2007 so that my spouse/partner
fields: FirstName, LastName, FirstName2, and LastName2 will print out as:
John Smith, John and Mary Smith, or John Smith and Mary Jones. (same
street/PO Box address)? The wizard doesn't seem to consider this an option,
and I don't know the syntax or programming code to accomplish this in design
view.

No code necessary.
Open Label report in design mode.
Move the controls below 'FirstName, LastName' down to allow the insertion of
another control.
Add a text box in the blank space.
Make sure the top of the added text box is at least 0.0007 below bottom of the
control above it and the bottom of the text box is at least 0.0007 above the
top of the control below it.
Set text box '=[FirstName2] & " " & [LastName2]
Set can shrink to yes.
If FirstName2 and LastName2 are Null,
the text box will shrink to zero height and the controls below will all move up
to fill the empty space.

Chuck
--

 

[John Spencer]

You might try an expression that looks like the following. You can use the
expression as the source for a control or you can use the expression in the
underlying query.

IIF(LastName2 Is Null and FirstName2 is Null,
FirstName & " " & LastName,
IIF(LastName <> LastName2,
Firstname & " " & LastName & " and " & FirstName2 & " " &
LastName2,
FirstName & " and " & FirstName2 & " " & LastName))

IF your fields can be zero length strings instead of null then you will need
to modify the comparison clauses to account for that

IIF (LastName2 & "" = "" and FirstName2 & "" = "", ...
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[JOSE ORTEGA]

HOLAAAAAAAAAAAAAAAAAAAAAA

 

[Judy]

This worked great as the source for a control. Just for my curiosity, how do
I use it in the query? Right now, I have a simple query to find all the Yes
answers to my mailing question, and I print labels from the query.

 

[John Spencer]

Just put the expression in the query. If you are using design view that
might look something like the following. Note that this is all one line.
Field: NamesForLabel: IIF(LastName2 Is Null and FirstName2 is Null,
FirstName & " " & LastName, IIF(LastName <> LastName2, Firstname & " " &
LastName & " and " & FirstName2 & " " & LastName2, FirstName & " and " &
FirstName2 & " " & LastName))

Those specific field names LastName, FirstName, LastName2, and FirstName2
must exist in your table or in an underlying source query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..