Problem creating greeting for a letter(access report)

[Al Kramer]

Hello Access Friends
I have a simple membership applicaion. I have imported legacy data, and all is working well. Now I'm working on output,letters(from Access report) to members. My problem arises with the creation of the greeting in the correspondence. The membership record can hold the name of two individuals.
If there is only one person in the record the greeting would be "Dear" prefix lname fname. At first I simply checked to see if lname2 was empty in order to determine if there was just one person in the record. That did not work because many records only record the first name of the "spouse". So the test for a single person is not empty fname2.

Pertinent fields Prefix, Lname, Fname ,Prefix1, Lname1, Fname1

There are four tests for the three possible greetings

1 Empty [Fname2] -> Mr John Smith
2 empty [Lname2} and not empty [Fname2] -> Mr & MRs John Smith
3 [Lname2] =[Lname] -> Mr & MRs John Smith
4 not empty[Lname2] and [Lname2] <> [Lname] -> Mr John Smith and Mr James Jones

Greet: IIf(IsNull([fName2]),[prefix]+" " & [Fname]+" " & [Lname]) works fine for test 1.

Can (should) I nest iif's deep enough to cover all four instances?

Is there a better way to do this? If VBA where would th code be placed? I don't have experience with VBA.

Any & all thoughts appreciated

Al

 

[DHookom]

I think the logic is complex enough that I would create a small user defined function that would accept the fields as arguments and return the properly formed greeting. This would allow you to use the function everywhere in your application. Functions can include comments regarding the logic as well as allowing you to easily debug your code.

I’m replying on a my iPad so I don’t have the ability to write the function for you. You could start by creating a new module with a function outline and your comments. Then add some If statements etc. Learn how to set a breakpoint and step through your code. You can test multiple scenarios in the Immediate/debug window. Now’s the time to learn a little a little VBA

 

[MajP]

Public Function GetOutPut(Fname As Variant, Lname As Variant, Fname2 As Variant, Lname2 As Variant) As String
  'make sure at least one
  If Not (IsNull(Lname) And IsNull(Lname2)) Then
    If IsNull(Fname2) Then
      GetOutPut = "Mr. " & Fname & " " & Lname
    ElseIf IsNull(Lname2) Or (Lname2 = Lname) Then
      GetOutPut = "Mr. & Mrs. " & Fname & " " & Lname
    ElseIf Lname2 <> Lname Then
      GetOutPut = "Mr. " & Fname & " " & Lname & " and Mrs." & Fname2 & " " & Lname2
    End If
 End If
End Function

SELECT Table1.Fname,
Table1.Lname,
Table1.Fname2,
Table1.LName2,
GetOutput([fname],[lname],[fname2],[lname2]) AS Invite
FROM Table1;

 

[MajP]

However I assume you built this database in 1950. Since now a days you probably need a lot more choices for salutations, pronouns, possibilities. I would assume you really need at least a salutation 1 & 2 or a child tables with an individual record per person.

 

[DHookom]

I took MajP's code and paste it into a module named modStrings (from the new Northwinds template). I then opened the debug window by pressing Ctrl+G. From there you can test the functions with different values including Null.

 

[MajP]

Also. I am sure this can be done in a nested iif. It just gets so tedious and frustrating trying to get the syntax correct. Building a UDF lets you test each piece much easier. A nested iif is probably more efficient and portable, but a pain to make especially with all of those concatenation characters.

 

[June7]

Need to add a space after "Mrs."

 

[Mark_]

Were this me, I would move the names to a child table FIRST. That way for a given membership you can have one or more people who are associated with that membership.

Then you can use this child table to drive your letters.

Hate to send "Mr. and Mrs. John Smith" to John and his sister.

 

[arnelgp]

you can also use this:

Greet:  Switch(IsNull([Fname2]),[Prefix] & " " & [Fname] & " " & [Lname],[Lname]=[Lname2],[Prefix] & " & " & [Prefix2] & " " & [Fname] & " " & [Lname],True,[Prefix] & " " & [Fname] & " " & [Lname] & " and " & [Prefix2] & " " & [Fname2] & " " & [Lname2])

sample data:

ID	Prefix	Fname	Lname	Prefix2	Fname2	Lname2
1	Mr.	John	Smith	Mrs.	X	Smith
2	Mr.	John	Smith
3	Mr.	John	Smith	Mrs.	X	Y

Result:

Greet

Mr. & Mrs. John Smith

Mr. John Smith

Mr. John Smith and Mrs. X Y

 

[Al Kramer]

I think the logic is complex enough that I would create a small user defined function that would accept the fields as arguments and return the properly formed greeting. This would allow you to use the function everywhere in your application. Functions can include comments regarding the logic as well as allowing you to easily debug your code.

I’m replying on a my iPad so I don’t have the ability to write the function for you. You could start by creating a new module with a function outline and your comments. Then add some If statements etc. Learn how to set a breakpoint and step through your code. You can test multiple scenarios in the Immediate/debug window. Now’s the time to learn a little a little VBA

Thank you

 

[Al Kramer]

However I assume you built this database in 1950. Since now a days you probably need a lot more choices for salutations, pronouns, possibilities. I would assume you really need at least a salutation 1 & 2 or a child tables with an individual record per person.

The Database is new but is based on the structure & options inherint to a previous MUCH older app.

 

[Al Kramer]

Were this me, I would move the names to a child table FIRST. That way for a given membership you can have one or more people who are associated with that membership.

Then you can use this child table to drive your letters.

Hate to send "Mr. and Mrs. John Smith" to John and his sister.

That sounds like very good advice...Thanx

 

[Al Kramer]

I took MajP's code and paste it into a module named modStrings (from the new Northwinds template). I then opened the debug window by pressing Ctrl+G. From there you can test the functions with different values including Null.

View attachment 118359

Also. I am sure this can be done in a nested iif. It just gets so tedious and frustrating trying to get the syntax correct. Building a UDF lets you test each piece much easier. A nested iif is probably more efficient and portable, but a pain to make especially with all of those concatenation characters.

I understand & agree .., is a UDF in Access sql or vba or either? & I'm not sure where to place the code??
Thanx

 

[GPGeorge]

Were this me, I would move the names to a child table FIRST. That way for a given membership you can have one or more people who are associated with that membership.

Then you can use this child table to drive your letters.

Hate to send "Mr. and Mrs. John Smith" to John and his sister.

A long time ago, one of my clients actually had this problem. We obtained membership lists from associations to conduct direct mail marketing. A monthly mailing could include 20,000 to 50,00 letters.

It turns out that more than one brother and sister shared a residence, more than one parent and adult child shared a residence, not all partners are married, not all partners are male and female, and more than one woman objected to being reduced to "Mrs. John Smith".

We got responses to mailings, and not all of them were happy.

Data cleansing is important, but being thoughtful about how you concatenate individual names is equally important.

 

[DickyP]

Not only is data cleansing important but a data design is critical - understood that legacy data is a problem and a revised data design may not be possible, when a potentially complicated function may be the only remedy. Much, much to better to re-design the data if possible.

 

[GaP42]

For me it is up to the individual when they sign up to a membership to specify/nominate a salutation, which is optional. Don't make assumptions.
Communications with the member reflect their choices - not yours. If memberships are family-based, or some other grouping (eg. organisation representative), then the person to receive communications - the primary contact - is addressed (if that is the way in which such memberships are managed). Otherwise you rely upon assumed relationship of those who live at the same address and individual salutation choices. Potentially confusing combinations might be resolved through some record of the relationship between members in a family to resolve the problem (self referential grouping of members of type "family" with a FamilyName - where someone could be nominated as the primary contact - however I only use this myself to ascertain eligibility for family membership - and application of discounted fees)

Legacy data, where relationship assumptions might be implied, may be cleaned by applying a process requiring members to review their choices.

If membership communications are by email I assume this issue may not be such a problem - the individual member has an email address or not. If an email address is shared by two or more members - they get the communication more than one to the email address. If they did not provide an email address they get the snail mail - even if the other members of the family received theirs by email. It was their preference, as was the salutation used in the email/mail addressed specifically to them.

 

[LarryE]

Hello Access Friends
I have a simple membership applicaion. I have imported legacy data, and all is working well. Now I'm working on output,letters(from Access report) to members. My problem arises with the creation of the greeting in the correspondence. The membership record can hold the name of two individuals.
If there is only one person in the record the greeting would be "Dear" prefix lname fname. At first I simply checked to see if lname2 was empty in order to determine if there was just one person in the record. That did not work because many records only record the first name of the "spouse". So the test for a single person is not empty fname2.

Pertinent fields Prefix, Lname, Fname ,Prefix1, Lname1, Fname1

There are four tests for the three possible greetings

1 Empty [Fname2] -> Mr John Smith
2 empty [Lname2} and not empty [Fname2] -> Mr & MRs John Smith
3 [Lname2] =[Lname] -> Mr & MRs John Smith
4 not empty[Lname2] and [Lname2] <> [Lname] -> Mr John Smith and Mr James Jones

Greet: IIf(IsNull([fName2]),[prefix]+" " & [Fname]+" " & [Lname]) works fine for test 1.

Can (should) I nest iif's deep enough to cover all four instances?

Is there a better way to do this? If VBA where would th code be placed? I don't have experience with VBA.

Any & all thoughts appreciated

Al

I would just use Dear Member:

 

[Al Kramer]

I would just use Dear Member:

Hey Larry
That was my first idea but the board of the Friends of the Library didn't go for it.

This looks like the end of this discussion. My problem is more than solved. Code supplied here worked without a hiccup AND i have had several suggestions that will lead me to explore other Access methodologies. I can't thank all of you enough. This site and it's members willingness to give time to help others is amazing

Again

Thanx

Al