Firstname plus Middlename??

  • Thread starter Thread starter KG
  • Start date Start date
K

KG

Apologies if this is a silly question. I'm trying to combine Lastname,
Firstname Middlename in a report. I have
=([surname]+", "+[firstname]+" "+[middlename]).
The only time it works is if all three fields contain values. As the
majority of players don't have middle names in the database, most of the
report is blank.
Can anyone tell me what I'm doing wrong?
Thanks so much for your help.
Kind regards...
 
There are several ways including an IIF statement.

=[surname] &", " & IIF([middlename] Is Null OR [middlename] = "",
[firstname], [firstname] & " " & [middlename])
 
You should be using the & concatenate operator instead of the + operator.

The plus returns blank if any of the values are null (blank/never entered).

The & operator treats nulls as if they were a zero-length string ("") and so
has no problem if any of the fields are blank - other than you may get some
unwanted spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Duane's expression will give a space after the name if there is no middle
name. The expression should be:
=[surname] & ", " & [firstname] + (" " & [middlename])

When the plus sign is followed by parantheses, if anything is null inside
the parantheses the whole value inside the parantheses becomes null. Thus,
if there is no middle name, the string ends at firstname and there is no
trailing space.

Steve
(e-mail address removed)

Duane Hookom said:
Try
=[surname] & ", " & [firstname] & " " + [middlename]

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at
http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


KG said:
Apologies if this is a silly question. I'm trying to combine Lastname,
Firstname Middlename in a report. I have
=([surname]+", "+[firstname]+" "+[middlename]).
The only time it works is if all three fields contain values. As the
majority of players don't have middle names in the database, most of the
report is blank.
Can anyone tell me what I'm doing wrong?
Thanks so much for your help.
Kind regards...
Click to expand...
Click to expand...
 
Thank you so much! As they say, "a little knowledge is a dangerous thing".
It all is working perfectly.


John Spencer said:
You should be using the & concatenate operator instead of the + operator.

The plus returns blank if any of the values are null (blank/never entered).

The & operator treats nulls as if they were a zero-length string ("") and so
has no problem if any of the fields are blank - other than you may get some
unwanted spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Apologies if this is a silly question. I'm trying to combine Lastname,
Firstname Middlename in a report. I have
=([surname]+", "+[firstname]+" "+[middlename]).
The only time it works is if all three fields contain values. As the
majority of players don't have middle names in the database, most of the
report is blank.
Can anyone tell me what I'm doing wrong?
Thanks so much for your help.
Kind regards...
Click to expand...
.
Click to expand...
 
Steve,
You have made an error in your expression.

" " & MiddleName will always return at least a space.
The plus sign will see the space and return it.

If you wanted to be safe you could use
[surname] & ", " & [firstname] & (" " + [middlename])

Although you could still end up with extra spaces if firstName and middlename
are null you would end up with
Spencer, and a space. "Spencer, "


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Duane's expression will give a space after the name if there is no middle
name. The expression should be:
=[surname] & ", " & [firstname] + (" " & [middlename])

When the plus sign is followed by parantheses, if anything is null inside
the parantheses the whole value inside the parantheses becomes null. Thus,
if there is no middle name, the string ends at firstname and there is no
trailing space.

Steve
(e-mail address removed)

Duane Hookom said:
Try
=[surname] & ", " & [firstname] & " " + [middlename]

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at
http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


KG said:
Apologies if this is a silly question. I'm trying to combine Lastname,
Firstname Middlename in a report. I have
=([surname]+", "+[firstname]+" "+[middlename]).
The only time it works is if all three fields contain values. As the
majority of players don't have middle names in the database, most of the
report is blank.
Can anyone tell me what I'm doing wrong?
Thanks so much for your help.
Kind regards...
Click to expand...
Click to expand...
Click to expand...
 
Steve,
Apparently you didn't try my suggestion (or yours) before posting. I tested
both of our expressions. I suggest you also test them.

The whole value inside your parantheses (" " & [middlename]) will never be
null. At the very least, it will be a single space.

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


Steve said:
Duane's expression will give a space after the name if there is no middle
name. The expression should be:
=[surname] & ", " & [firstname] + (" " & [middlename])

When the plus sign is followed by parantheses, if anything is null inside
the parantheses the whole value inside the parantheses becomes null. Thus,
if there is no middle name, the string ends at firstname and there is no
trailing space.

Steve
(e-mail address removed)

Duane Hookom said:
Try
=[surname] & ", " & [firstname] & " " + [middlename]

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at
http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


KG said:
Apologies if this is a silly question. I'm trying to combine Lastname,
Firstname Middlename in a report. I have
=([surname]+", "+[firstname]+" "+[middlename]).
The only time it works is if all three fields contain values. As the
majority of players don't have middle names in the database, most of the
report is blank.
Can anyone tell me what I'm doing wrong?
Thanks so much for your help.
Kind regards...
Click to expand...
Click to expand...


.
Click to expand...
 
You should be using the & concatenate operator instead of the +
operator.

The plus returns blank if any of the values are null (blank/never
entered).

The & operator treats nulls as if they were a zero-length string
("") and so has no problem if any of the fields are blank - other
than you may get some unwanted spaces.
Click to expand...

Put another way, the + operator used with strings propagates Nulls.
That is actually quite useful, and I capitalize on it all the time.
For example:

Mid(("12"+LastName) & (", "+FirstName), 3)

That will produce correct results when neither field is Null, when
both are Null, and when either is Null and the other is not. It's
much more efficient and easy to read than the nested IIf() tests
that would otherwise be required.

Beware, though, that if a field has a string in it that is coercable
to a numeric value, you may end up with addition instead of
concatenation. For instance, "12"+LastName could produce a numeric
100012 if, for instance, the LastName field contained the string
"100000". This implicit type coercion is often quite helpful, but in
this case, it wouldn't be.

Note that it won't happen in *all* cases -- that very
unpredictability is the reason why you have to be careful with it.
 
There are several ways including an IIF statement.

=[surname] &", " & IIF([middlename] Is Null OR [middlename] = "",
[firstname], [firstname] & " " & [middlename])
Click to expand...

But capitalizing on the Null propagation of the + concatenation
operator can make for something much simpler (and more reliable in
producing proper results):

Mid(("12"+LastName) & (", "+(Trim(FirstName & (" "+MiddleName)))),
3)

This handles more combinations of Nulls and non-Nulls than the
formula above.

For what it's worth, I long ago stopped storing middle initial/name
as a separate field, as it's not an independent piece of data. The
only scenario in which it's useful to separate it out is when you
want to construct a salutation field -- "Dear David" is much nicer
than "Dear David W.", of course. But you then gain another problem
because you end up with the ambiguity of where to put data when
someone has more than one candidate middle name/initial.

I instead store a Salutation field so that where the default value
is inappropriate, the user can fill in a proper value. This also
allows for salutations not permanently tied to the name fields, such
as Robert Smith's salutation can be "Dear Bob". To me, structuring
your name storage for the purpose of constructing a saluation field
is a wrong design. A salutation field is sufficiently independent to
get its own data field, and that allows far more flexibility than is
possible with deriving it from the name fields, and also allows the
name storage to be simpler.
 
You have made an error in your expression.

" " & MiddleName will always return at least a space.
The plus sign will see the space and return it.

If you wanted to be safe you could use
[surname] & ", " & [firstname] & (" " + [middlename])

Although you could still end up with extra spaces if firstName and
middlename are null you would end up with
Spencer, and a space. "Spencer, "
Click to expand...

This solves all the possibilities:

Mid(("12"+[LastName]) & (", "+(Trim([FirstName]
& (" "+[MiddleName])))),3)
 
Back
Top