If FredG's suggestion did not work and you still get two spaces then you will
need to use an expression like the following.
CombinedNames:[First Name] & IIF({MiddleName] & "")= "",""," ") &
[MiddleName]) & " " & [LastName]
Fred's version relies on MiddleName being NULL. It could be a zero-length
string which is a different thing. The above IIF clause tests for both nulls
and zero-length strings. You can check to see if Zero-length strings are
allowed by looking at the field properties in your table.
--
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thank you very much for your response. However, this works fine when there
is a MidNM or Initial and period. But when there is no MidNM, then it leaves
2 spaces instead of just one space between first Name and last name. Can
this be fixed? if so how? Thank you.
:
On Thu, 26 Jun 2008 12:51:37 -0700, TotallyConfused wrote:
How do I write a query that say the following: If there is a middle name,
then Middle Name and space. If there is not a middle name, then no space. I
am trying to concactenate First, Middle and Last Names. But do not want a
double space if there is no middle name. Any help will be great appreciated
Thank you.
CominedNames:[First Name] & (" "+[MiddleName]) & " " & [LastName]
Did you use the + symbol and parenthesis as I posted?
(" "+[MiddleName])
It works fine for me.
John Smith
John C. Smith
Please copy and paste the exact expression you are using.
You can also use:
CombinedNames:[FirstName] & " " &
IIf(IsNull([MiddleInitial]),[LastName],[MiddleInitial] & " " &
[LastName])
