Display concatenated name with conditional commas

[SherryScrapDog]

I have a database that is full of names (for genealogy) and I am displaying
the names on multiple screens in one field by concatinating the name fields
together. My fields are Last, First, Middle, Title. The Last name contains
some business names, which means there are no First, Middle or Title. I am
now using this in the control for each form: =Last & ", "&First&" " &
Middle& "" &Title. My problem is I get the comma on the business names. I
would like to make this conditional and did find one post that said: Expr1:
([Last]) & (", " + [First]) & (" " + [Middle]) & (", "+[Title])) , and I
tried this in the control and I end up with more commas. Probably because
I'd like to also put a comma before the Title, if it exists. Maybe this
isn't intended to put in the control property? Ideally, I would like a
public module where I could put the code to concatinate and use in my
multiple forms, but I do not know how to do this. I don't know what to put
in the module, and I don't know what I would put in the control. Do I need
to use If-Then to decide how to concatinate the name? Any help would be
appreciated. Thanks! Sherry

 

[Baz]

I think this might do it (untested):

[Last] & (", " + Trim((([First] & (" " + [Middle])) + ", ") & [Title]))

 

[SherryScrapDog]

Hi Baz,
Thanks for the attempt! I still get the 2 commas with this no matter what
data I have. Sherry

I think this might do it (untested):

[Last] & (", " + Trim((([First] & (" " + [Middle])) + ", ") & [Title]))

I have a database that is full of names (for genealogy) and I am displaying
the names on multiple screens in one field by concatinating the name
fields
together. My fields are Last, First, Middle, Title. The Last name
contains
some business names, which means there are no First, Middle or Title. I
am
now using this in the control for each form: =Last & ", "&First&" " &
Middle& "" &Title. My problem is I get the comma on the business names.
I
would like to make this conditional and did find one post that said:
Expr1:
([Last]) & (", " + [First]) & (" " + [Middle]) & (", "+[Title])) , and I
tried this in the control and I end up with more commas. Probably because
I'd like to also put a comma before the Title, if it exists. Maybe this
isn't intended to put in the control property? Ideally, I would like a
public module where I could put the code to concatinate and use in my
multiple forms, but I do not know how to do this. I don't know what to
put
in the module, and I don't know what I would put in the control. Do I
need
to use If-Then to decide how to concatinate the name? Any help would be
appreciated. Thanks! Sherry

 

[Baz]

Under what circumstances? Can you give an example of data for which you get
too many commas?

My suggestion (and the one you received previously) assumed that the missing
parts of the name would be Null. Is is possible that they might contain
zero-length strings or spaces? If so, a different solution will be
required.

Hi Baz,
Thanks for the attempt! I still get the 2 commas with this no matter
what
data I have. Sherry

I think this might do it (untested):

[Last] & (", " + Trim((([First] & (" " + [Middle])) + ", ") & [Title]))

message

I have a database that is full of names (for genealogy) and I am
displaying
the names on multiple screens in one field by concatinating the name
fields
together. My fields are Last, First, Middle, Title. The Last name
contains
some business names, which means there are no First, Middle or Title.
I
am
now using this in the control for each form: =Last & ", "&First&" " &
Middle& "" &Title. My problem is I get the comma on the business
names.
I
would like to make this conditional and did find one post that said:
Expr1:
([Last]) & (", " + [First]) & (" " + [Middle]) & (", "+[Title])) , and
I
tried this in the control and I end up with more commas. Probably
because
I'd like to also put a comma before the Title, if it exists. Maybe
this
isn't intended to put in the control property? Ideally, I would like a
public module where I could put the code to concatinate and use in my
multiple forms, but I do not know how to do this. I don't know what to
put
in the module, and I don't know what I would put in the control. Do I
need
to use If-Then to decide how to concatinate the name? Any help would
be
appreciated. Thanks! Sherry

 

[Douglas J. Steele]

[Last] & (", " + [First]) & (" " + [Middle]) & (", " + [Title])

should work. What's some sample data, and what's it giving you?

If you really need this in a public module, you'd need something like

Function ConcatenateName( _
LastName As Variant, _
FirstName As Variant, _
MiddleName As Variant, _
Title As Variant _
) As String

ConcatenateName = LastName & _
(", " + FirstName) & _
(" " + MiddleName) & _
(", " + Title)

End Sub

You'd than use

=ConcatenateName([Last], [First], [Middle], [Title])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Baz,
Thanks for the attempt! I still get the 2 commas with this no matter
what
data I have. Sherry

I think this might do it (untested):

[Last] & (", " + Trim((([First] & (" " + [Middle])) + ", ") & [Title]))

message

I have a database that is full of names (for genealogy) and I am
displaying
the names on multiple screens in one field by concatinating the name
fields
together. My fields are Last, First, Middle, Title. The Last name
contains
some business names, which means there are no First, Middle or Title.
I
am
now using this in the control for each form: =Last & ", "&First&" " &
Middle& "" &Title. My problem is I get the comma on the business
names.
I
would like to make this conditional and did find one post that said:
Expr1:
([Last]) & (", " + [First]) & (" " + [Middle]) & (", "+[Title])) , and
I
tried this in the control and I end up with more commas. Probably
because
I'd like to also put a comma before the Title, if it exists. Maybe
this
isn't intended to put in the control property? Ideally, I would like a
public module where I could put the code to concatinate and use in my
multiple forms, but I do not know how to do this. I don't know what to
put
in the module, and I don't know what I would put in the control. Do I
need
to use If-Then to decide how to concatinate the name? Any help would
be
appreciated. Thanks! Sherry

 

[SherryScrapDog]

Hi Baz,
Yes, my fields allow zero-length (to prevent duplicate loading of the
combined names in the Master file, which I had help with from this site).
I'm sorry that I don't know enough about the difference between Null and
zero-length to put this in my question. I'll give you a couple of examples,
but I assume you may not need them since you already said I would need a
different solution.
Last = Smith, First = Jane: I get Smith, Jane,
Last = Business Solutions: I get Business Solutions, ,
Thanks for helping and sorry for the confusion!! Sherry

Under what circumstances? Can you give an example of data for which you get
too many commas?

My suggestion (and the one you received previously) assumed that the missing
parts of the name would be Null. Is is possible that they might contain
zero-length strings or spaces? If so, a different solution will be
required.

Hi Baz,
Thanks for the attempt! I still get the 2 commas with this no matter
what
data I have. Sherry

I think this might do it (untested):

[Last] & (", " + Trim((([First] & (" " + [Middle])) + ", ") & [Title]))

message
I have a database that is full of names (for genealogy) and I am
displaying
the names on multiple screens in one field by concatinating the name
fields
together. My fields are Last, First, Middle, Title. The Last name
contains
some business names, which means there are no First, Middle or Title.
I
am
now using this in the control for each form: =Last & ", "&First&" " &
Middle& "" &Title. My problem is I get the comma on the business
names.
I
would like to make this conditional and did find one post that said:
Expr1:
([Last]) & (", " + [First]) & (" " + [Middle]) & (", "+[Title])) , and
I
tried this in the control and I end up with more commas. Probably
because
I'd like to also put a comma before the Title, if it exists. Maybe
this
isn't intended to put in the control property? Ideally, I would like a
public module where I could put the code to concatinate and use in my
multiple forms, but I do not know how to do this. I don't know what to
put
in the module, and I don't know what I would put in the control. Do I
need
to use If-Then to decide how to concatinate the name? Any help would
be
appreciated. Thanks! Sherry

 

[Baz]

Hi Sherry,

What a fiddly problem. My suggestion doesn't work because I assumed that
there would always be a title if there was always a first/middle name.
Doug's solution nearly works but the first comma is missing if there is a
middle name but no first name.

Here's a rather less elegant attempt which I think gets it all and also
handles zero-length and space-filled fields (use the function as described
in Doug's post):

Function ConcatenateName( _
LastName As Variant, _
FirstName As Variant, _
MiddleName As Variant, _
Title As Variant _
) As String

If Trim(Nz(LastName)) <> "" Then
If Trim(Nz(FirstName)) <> "" Or Trim(Nz(MiddleName)) <> "" Then
ConcatenateName = Trim(Nz(LastName)) & ", " &
Trim(Trim(Nz(FirstName)) & " " & Trim(Nz(MiddleName)))
Else
ConcatenateName = Trim(Nz(LastName))
End If
Else
ConcatenateName = Trim(Trim(Nz(FirstName)) & " " &
Trim(Nz(MiddleName)))
End If
If ConcatenateName <> "" Then
If Trim(Nz(Title)) <> "" Then
ConcatenateName = ConcatenateName & ", " & Trim(Nz(Title))
End If
Else
ConcatenateName = Trim(Nz(Title))
End If

End Function

Hi Baz,
Yes, my fields allow zero-length (to prevent duplicate loading of the
combined names in the Master file, which I had help with from this site).
I'm sorry that I don't know enough about the difference between Null and
zero-length to put this in my question. I'll give you a couple of
examples,
but I assume you may not need them since you already said I would need a
different solution.
Last = Smith, First = Jane: I get Smith, Jane,
Last = Business Solutions: I get Business Solutions, ,
Thanks for helping and sorry for the confusion!! Sherry

Under what circumstances? Can you give an example of data for which you
get
too many commas?

My suggestion (and the one you received previously) assumed that the
missing
parts of the name would be Null. Is is possible that they might contain
zero-length strings or spaces? If so, a different solution will be
required.

message

Hi Baz,
Thanks for the attempt! I still get the 2 commas with this no matter
what
data I have. Sherry

:

I think this might do it (untested):

[Last] & (", " + Trim((([First] & (" " + [Middle])) + ", ") &
[Title]))

message
I have a database that is full of names (for genealogy) and I am
displaying
the names on multiple screens in one field by concatinating the name
fields
together. My fields are Last, First, Middle, Title. The Last name
contains
some business names, which means there are no First, Middle or
Title.
I
am
now using this in the control for each form: =Last & ", "&First&" "
&
Middle& "" &Title. My problem is I get the comma on the business
names.
I
would like to make this conditional and did find one post that
said:
Expr1:
([Last]) & (", " + [First]) & (" " + [Middle]) & (", "+[Title])) ,
and
I
tried this in the control and I end up with more commas. Probably
because
I'd like to also put a comma before the Title, if it exists. Maybe
this
isn't intended to put in the control property? Ideally, I would
like a
public module where I could put the code to concatinate and use in
my
multiple forms, but I do not know how to do this. I don't know what
to
put
in the module, and I don't know what I would put in the control. Do
I
need
to use If-Then to decide how to concatinate the name? Any help
would
be
appreciated. Thanks! Sherry

 

[SherryScrapDog]

Thanks Baz!!! Works great! I shouldn't have a middle name without a first
name, however, it never hurts to code for it especially since the data comes
from many spreadsheet files originally. Many thanks for all of your help!
Sherry

 

[SherryScrapDog]

Hi Doug,
Thanks so much for this! I now have the names displaying just how I want
them to! Sherry

 

[Baz]

Indeed Sherry, it always pays to expect the unexpected. Glad you got it
working.

Baz