Display concatenated name with conditional commas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SherryScrapDog - 01 Jan 2008 16:18 GMT

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

Reply to this Message

Baz - 01 Jan 2008 16:40 GMT

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
[quoted text clipped - 19 lines]
> to use If-Then to decide how to concatinate the name? Any help would be
> appreciated. Thanks! Sherry

Reply to this Message

SherryScrapDog - 01 Jan 2008 17:12 GMT

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):
>
[quoted text clipped - 23 lines]
> > to use If-Then to decide how to concatinate the name? Any help would be
> > appreciated. Thanks! Sherry

Reply to this Message

Baz - 01 Jan 2008 17:23 GMT

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
[quoted text clipped - 35 lines]
>> > be
>> > appreciated. Thanks! Sherry

Reply to this Message

SherryScrapDog - 01 Jan 2008 17:39 GMT

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?
[quoted text clipped - 43 lines]
> >> > be
> >> > appreciated. Thanks! Sherry

Reply to this Message

Baz - 01 Jan 2008 18:44 GMT

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
[quoted text clipped - 67 lines]
>> >> > be
>> >> > appreciated. Thanks! Sherry

Reply to this Message

SherryScrapDog - 01 Jan 2008 19:45 GMT

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

> 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.

Reply to this Message

Baz - 01 Jan 2008 22:51 GMT

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

Baz

> Thanks Baz!!! Works great! I shouldn't have a middle name without a
> first
[quoted text clipped - 9 lines]
>> Doug's solution nearly works but the first comma is missing if there is a
>> middle name but no first name.

Reply to this Message

Douglas J. Steele - 01 Jan 2008 17:33 GMT

[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])

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi Baz,
> Thanks for the attempt! I still get the 2 commas with this no matter
[quoted text clipped - 35 lines]
>> > be
>> > appreciated. Thanks! Sherry

Reply to this Message

SherryScrapDog - 01 Jan 2008 19:47 GMT

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

> [Last] & (", " + [First]) & (" " + [Middle]) & (", " + [Title])
>
> should work. What's some sample data, and what's it giving you?

Reply to this Message