Solved

Access 2010: Query to concatenate fields with a space

Posted on 2013-12-09
3
604 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
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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now