A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Firstname plus Middlename??



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 06:31 PM posted to microsoft.public.access.reports
KG
external usenet poster
 
Posts: 83
Default 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...
Ads
  #2  
Old June 3rd, 2010, 06:54 PM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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...

  #3  
Old June 3rd, 2010, 07:10 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default 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...

  #4  
Old June 3rd, 2010, 07:44 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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...

  #5  
Old June 3rd, 2010, 08:37 PM posted to microsoft.public.access.reports
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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...



  #6  
Old June 3rd, 2010, 08:56 PM posted to microsoft.public.access.reports
KG
external usenet poster
 
Posts: 83
Default 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...

.

  #7  
Old June 3rd, 2010, 09:30 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default 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...



  #8  
Old June 4th, 2010, 02:54 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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...



.

  #9  
Old June 4th, 2010, 06:52 PM posted to microsoft.public.access.reports
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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/
  #10  
Old June 4th, 2010, 07:01 PM posted to microsoft.public.access.reports
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.