mail merge addressee tangle

[soojosie]

Hi,
I have an access table with 3 different types of addressees.
1. Couples with same LastName
2. Couples with different LastNames
3. Individuals.

I'm trying to use mail merge.
But it can't differentiate between the 3 different varieties
what I would like is:
1. Anna & Jay WHITE - This works O.K.
2. Marie SMITH & Katie BROWN - Here I'm get Marie & Katie SMITH
3. Jason BLACK - weird but here I get “The BLACK Family”??!!
this is when using the “Insert Address block” function in mail merge - and yes I've tried all the options.

How can I ensure all members are addressed correctly?

Do I need to run separate queries? Is there a better way?

cheers

 

[adam_fleck]

Applications regularly surprise me in just the way you describe. Although I know they only do what you tell them to do, I regularly find myself thinking "Did I really tell it to do that?"

You should be able to set up a query in Access that will set up the details you need for your mailmerge, and you can then use this query as your merge data source. Controlling the details from Access will probably be easier than trying to control them from Word.

In the top line of the Access query design grid you could set up a new calculated column by typing in something like this:

name_line: iif([addressee_type] = 1, [first_name_1] & " and " & [first_name_2] & " " [last_name_1],iif([addresse_type]=2,"Something Similar","Something really quite complex"))

Your best bet however, might be to open a module and write a function that will set the name_line for you:

Public Function set_Name_Line(int_type As Integer, _
                              str_FN1 As String, str_FN2 As String, _
                              str_SN1 As String, str_SN2 As String)

    Dim str_Name_Line As String

    Select Case int_type
    
        Case 1
                str_Name_Line = str_FN1 & " and " & str_FN2 & " " & str_SN1

        Case 2
                '...

        Case 3
                '...

    End Select

    set_Name_Line = str_Name_Line
    
End Function

You can then call this function from your query by setting up a new calculated column -

name_line: set_Name_Line(addressee_type, [FN_1], [FN_2], [SN_1], [SN_2])

Though you may find you need to use nZ([FN_1],"") in place of [FN_1] etc. to make sure that no Nulls are passed to your function.

 

[soojosie]

mail merge still a little tangled

Hi adam_fleck,

I'm still a little tangled.

I've put in the query and that works great.
but when I've open a module as below


"
Public Function set_NameLine(int_type As Integer, str_FirstName1 As String, _
str_FirstName2 As String, str_LastName1 As String, str_FirstName2 As String, _
str_LastName2 As String)

Dim str_NameLine As String

Select Case int_type

Case1
str_NameLine = str_FirstName1 & " and " & str_FirstName2 & " " & str_LastName1

Case2
str_NameLine = str_FirstName1 & " " & str_LastName1 & " and " & str_FirstName2 & " " & str_LastName2

Case3
str_NameLine = str_FirstName1 & " " & str_LastName1

End Select

set_NameLine = str_NameLine

End Function"

that seems OK

But when I enter the following in a query i'm missing something.

"NameLine: set_NameLine(addressee_type, [FirstName1], [FirstName2], [LastName1], [LastName2])"

It gets changed to

"NameLine: set_NameLine([addressee_type],[FirstName1],[FirstName2],[LastName1],[LastName2])"

but still will not accept it. I've not called up a function in this way before so I'm not real sure what I'm aiming for.

adam, Will I now have to enter NameType against each record? Or create 3 separate tables for each Name Type? Or? Or?
Sorry if I'm a bit slow
Thank you for your help.

Josie

 

[shay]

Hi there

You've got 'str_FirstName2 As String' twice in the argument list.

shay