Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 654
  • Last Modified:

Access 2010: Query to concatenate fields with a space

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
JAMES125
Asked:
JAMES125
1 Solution
 
Kelvin SparksCommented:
FirstInitial & IIf(IsNull(MiddleInitial," ', MIddleInitial) & LastName


Kelvin
0
 
mbizupCommented:
Minor correction to Kelvin's post - no points please.


FirstInitial & IIf(IsNull(MiddleInitial)," ", MIddleInitial) & LastName
0
 
PatHartmanCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now