Solved

Access 2010: Query to concatenate fields with a space

Posted on 2013-12-09
3
600 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
Comment Utility
FirstInitial & IIf(IsNull(MiddleInitial," ', MIddleInitial) & LastName


Kelvin
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Minor correction to Kelvin's post - no points please.


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

Expert Comment

by:PatHartman
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

6 Experts available now in Live!

Get 1:1 Help Now