OfficeFrustration

OfficeFrustration (http://www.officefrustration.com/index.php)
-   Setting Up & Running Reports (http://www.officefrustration.com/forumdisplay.php?f=26)
-   -   Firstname plus Middlename?? (http://www.officefrustration.com/showthread.php?t=1119271)

KG June 3rd, 2010 06:31 PM

Firstname plus Middlename??
 
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...

KARL DEWEY June 3rd, 2010 06:54 PM

Firstname plus Middlename??
 
There are several ways including an IIF statement.

=[surname] &", " & IIF([middlename] Is Null OR [middlename] = "",
[firstname], [firstname] & " " & [middlename])


--
Build a little, test a little.


"KG" wrote:

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


John Spencer June 3rd, 2010 07:10 PM

Firstname plus 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

KG wrote:
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...


Duane Hookom June 3rd, 2010 07:44 PM

Firstname plus Middlename??
 
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/...ddbuz/threads?


"KG" wrote:

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


Steve[_77_] June 3rd, 2010 08:37 PM

Firstname plus Middlename??
 
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


"Duane Hookom" wrote in message
...
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/...ddbuz/threads?


"KG" wrote:

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




KG June 3rd, 2010 08:56 PM

Firstname plus Middlename??
 
Thank you so much! As they say, "a little knowledge is a dangerous thing".
It all is working perfectly.


"John Spencer" wrote:

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

KG wrote:
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...

.


John Spencer June 3rd, 2010 09:30 PM

Firstname plus Middlename??
 
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

Steve wrote:
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


"Duane Hookom" wrote in message
...
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/...ddbuz/threads?


"KG" wrote:

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




Duane Hookom June 4th, 2010 02:54 PM

Firstname plus Middlename??
 
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/...ddbuz/threads?


"Steve" wrote:

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


"Duane Hookom" wrote in message
...
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/...ddbuz/threads?


"KG" wrote:

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



.


David W. Fenton June 4th, 2010 06:52 PM

Firstname plus Middlename??
 
John Spencer wrote in
:

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.


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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David W. Fenton June 4th, 2010 07:01 PM

Firstname plus Middlename??
 
=?Utf-8?B?S0FSTCBERVdFWQ==?=
wrote in :

There are several ways including an IIF statement.

=[surname] &", " & IIF([middlename] Is Null OR [middlename] = "",
[firstname], [firstname] & " " & [middlename])


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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


All times are GMT +1. The time now is 08:44 PM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
OfficeFrustration.com