Middle Initial Trick

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003 on Windows XP Pro

A common need when rebuilding a Full Name display from data fields involves
middle initials. Some people provide middle initials and some do not. The
following falls short of my objective, since when [MidInitial] is null, a
double space is created between the [FirstName] and [LastName], and that is
not desired.

=[FirstName] & " " & [MidInitial] & " " & [LastName]

Somewhere I saw a trick using the "+" sign where a null condition ignores
the extra space. I've tried the following syntax, but remain defeated.

=[FirstName] & (" ",+[MidInitial]) & [MidInitial] & " " & [LastName]

Any hints as to where I am going wrong, and the proper syntax?

...Thanks
 
The comma is incorrect, plus you only need MidInitial once:

=[FirstName] & (" " + [MidInitial]) & " " & [LastName]
 
Thanks Doug,

Even on Christmas Day your dedication and helpful spirit continue to shine.
As always, short, precise and exactly as needed.

Thanks ...and Best of the Season

Douglas J. Steele said:
The comma is incorrect, plus you only need MidInitial once:

=[FirstName] & (" " + [MidInitial]) & " " & [LastName]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The Grape Hunter said:
Access 2003 on Windows XP Pro

A common need when rebuilding a Full Name display from data fields
involves
middle initials. Some people provide middle initials and some do not.
The
following falls short of my objective, since when [MidInitial] is null, a
double space is created between the [FirstName] and [LastName], and that
is
not desired.

=[FirstName] & " " & [MidInitial] & " " & [LastName]

Somewhere I saw a trick using the "+" sign where a null condition ignores
the extra space. I've tried the following syntax, but remain defeated.

=[FirstName] & (" ",+[MidInitial]) & [MidInitial] & " " & [LastName]

Any hints as to where I am going wrong, and the proper syntax?

..Thanks
Click to expand...
Click to expand...
 
Douglas J. Steele said:
The comma is incorrect, plus you only need MidInitial once:

=[FirstName] & (" " + [MidInitial]) & " " & [LastName]
Click to expand...

For the sake of readability I prefer the use of Trim() in a case like this, like in:
FullName = [FirstName] & " " & Trim([MidInitial] & " " & [LastName])

or even a 'double-trim' (when also First Name is not provided)
FullName = Trim([FirstName] & " " & Trim([MidInitial] & " " & [LastName]))

Arno R
 
The said:
Thanks Doug,

Even on Christmas Day your dedication and helpful spirit continue to shine.
As always, short, precise and exactly as needed.

Thanks ...and Best of the Season

:

The comma is incorrect, plus you only need MidInitial once:

=[FirstName] & (" " + [MidInitial]) & " " & [LastName]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Access 2003 on Windows XP Pro

A common need when rebuilding a Full Name display from data fields
involves
middle initials. Some people provide middle initials and some do not.
The
following falls short of my objective, since when [MidInitial] is null, a
double space is created between the [FirstName] and [LastName], and that
is
not desired.

=[FirstName] & " " & [MidInitial] & " " & [LastName]

Somewhere I saw a trick using the "+" sign where a null condition ignores
the extra space. I've tried the following syntax, but remain defeated.

=[FirstName] & (" ",+[MidInitial]) & [MidInitial] & " " & [LastName]

Any hints as to where I am going wrong, and the proper syntax?

..Thanks
Click to expand...
Click to expand...
Click to expand...
How can one determine if a period was included with that middle initial?
If it was not, how can one add it?

Just curious.


--
Frederick Wilson

_____________________________________
for multimedia design services visit
http://www.legalanimatics.com
 
Frederick Wilson said:
How can one determine if a period was included with that middle initial?
If it was not, how can one add it?
Click to expand...


=[FirstName] & " " & IIf(Len(Trim([MidInitial] & "")) = 0, "",
IIf(Right([MidInitial], 1) = ".", [MidInitial] & " ", [MidInitial] & ". "))
& [LastName]

(Of course, this won't work for Harry S Truman.... <g>)
 
Douglas J. Steele said:
Frederick Wilson said:
How can one determine if a period was included with that middle
initial? If it was not, how can one add it?
Click to expand...


=[FirstName] & " " & IIf(Len(Trim([MidInitial] & "")) = 0, "",
IIf(Right([MidInitial], 1) = ".", [MidInitial] & " ", [MidInitial] &
". ")) & [LastName]

(Of course, this won't work for Harry S Truman.... <g>)
Click to expand...

I wonder how many people know that, Doug?
 
TC said:
I don't geddit! What's the problem with Harry S Truman?
Click to expand...

I should let Doug answer that, but I can't resist. Doug's code would
improperly put a period after the letter S, which is Truman's middle
name, *not* an initial.
 
Good lord! I never knew that. That is an //excellent// one, I'll
remember that for future reference.

A user of one of my systems had a problem recently. A customer had
deleted their surname! (through a legal instrument) I was a bit
surprised that this was legally possible. It caused a problem in my
system, where forname & surname were both required fields. So they
entered him with foname John, surname ".." !

TC
 
TC said:
A user of one of my systems had a problem recently. A customer had
deleted their surname! (through a legal instrument) I was a bit
surprised that this was legally possible. It caused a problem in my
system, where forname & surname were both required fields. So they
entered him with foname John, surname ".." !
Click to expand...

Huh. That's an odd one. I generally allow for an individual to have a
single name -- as, for example, "Prince" -- but I always stuff that name
into the surname field. I'm curious as to the legal status of such a
name. How do the law and the government view it? Does the former given
name effectively become the surname?
 
I wonder how many people know that, Doug?
Click to expand...

some of us old codgers do...

and some know the three-letter alternative middle name a bunch of his
opponents preferred.

John W. Vinson[MVP]
 
Good lord! I never knew that. That is an //excellent// one, I'll
remember that for future reference.

A user of one of my systems had a problem recently. A customer had
deleted their surname! (through a legal instrument) I was a bit
surprised that this was legally possible. It caused a problem in my
system, where forname & surname were both required fields. So they
entered him with foname John, surname ".." !

TC
Click to expand...

The (so-called) singer Madonna's name is just that: Madonna. Not too
uncommon in the entertainment world, and all but universal in some
cultures (Burma frex).

John W. Vinson[MVP]
 
I'm not sure how it is legal.

Interesting other examples from you & John Vinson. I'd never really
considered such cases. Perhaps "one name" entertainers do not do the
kind of things, that get recorded into my systems!

Cheers,
TC
 
John Nurick said:
Me neither. And maybe that's why I'm confused. Surely if Truman's middle
name was "S", his middle initial was "S."?
Click to expand...

From Wikipedia:

"Truman did not have a middle name, but only a middle initial. It was a
common practice in southern states, including Missouri, to use initials
rather than names. Truman said the initial was a compromise between the
names of his grandfathers, Anderson Shipp(e) Truman and Solomon Young. He
once joked that the S was a name, not an initial, and it should not have a
period, but official documents and his presidential library all use a
period. Furthermore, the Harry S. Truman Library has numerous examples of
the signature written at various times throughout Truman's lifetime where
his own use of a period after the "S" is very obvious."
 
Back
Top