Solved

Access 2010: Query to concatenate fields with a space

Posted on 2013-12-09
3
632 Views
Last Modified: 2013-12-13
I have a query to concatenate 3 fields "First Initial" + "Middle Initial" + "Last Name".  Sometimes there is no middle initial but I need a space to be in its place but it just joins the “First Initial” + the “Last Name” together.  Any suggestions?
0
Comment
Question by:JAMES125
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 39706947
FirstInitial & IIf(IsNull(MiddleInitial," ', MIddleInitial) & LastName


Kelvin
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39706951
Minor correction to Kelvin's post - no points please.


FirstInitial & IIf(IsNull(MiddleInitial)," ", MIddleInitial) & LastName
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39707259
The & (ampersand) is the standard VBA concatenation operator and should be used in most cases.  The  + (plus) does double duty as both an arithmetic operator and a concatenation operator but as a concatenation operator, it works differently from the & and it will not work at all as a concatenation operator if the two operands are numeric.  If they are numeric, it will perform its primary function which is to add them.  

The & ignores nulls and the + respects them.  Essentially, the & treats nulls as ZLS,  so

FN & MN & LN will return FNLN if MN is null OR ZLS
BUT
FN + MN + LN will return null if MN (or either of the other two fields) is null OR FNLN if MN is a ZLS.

That tells me that your MN column isn't null but instead contains a ZLS (ZeroLengthString) or you would be asking a different question.  

kevinsparks solution with mbizup's correction of the typo will work.  I just wanted to explain the difference in the way the two operators work since you were using the non-standard one.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question