Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-14-2018, 04:47 PM   #1
lhooker
Newly Registered User
 
Join Date: Dec 2005
Posts: 274
Thanks: 51
Thanked 1 Time in 1 Post
lhooker is on a distinguished road
Concatenation Not Working

Why is no 'Actual_Name' given when no 'Middle_Name' is provided in a select query ? Below is the syntax of the statement.

Actual_Name: [First_Name]+" "+[Middle_Name]+" "+[Last_Name]


lhooker is offline   Reply With Quote
Old 04-14-2018, 05:04 PM   #2
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 201
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Concatenation Not Working

What is your complete SQL statement?
What is your actual results?
What is your expected results?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
The Following User Says Thank You to BigHappyDaddy For This Useful Post:
lhooker (04-16-2018)
Old 04-14-2018, 05:08 PM   #3
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,531
Thanks: 72
Thanked 1,113 Times in 1,037 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Concatenation Not Working

Try this instead:

Code:
Actual_Name: [First_Name] & IIf(Nz([Middle_Name],"")<>""," " & [Middle_Name] & " "," ") & [Last_Name]
Notice I've replaced + separator with &
Also modified the handling of spaces around the middle name so you don't get two spaces if no middle name exists

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
lhooker (04-16-2018)
Old 04-14-2018, 06:00 PM   #4
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,786
Thanks: 52
Thanked 1,869 Times in 1,784 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Concatenation Not Working

Because it propagate the null when you use +, instead:

Actual_Name: ([First_Name] + " ") & ( [Middle_Name]) + " ") & [Last_Name]
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following 2 Users Say Thank You to arnelgp For This Useful Post:
lhooker (04-16-2018), Uncle Gizmo (04-14-2018)
Old 04-14-2018, 06:55 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,841
Thanks: 13
Thanked 1,299 Times in 1,238 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Concatenation Not Working

The & is the standard VBA concatenation operator and Access also uses it in queries. The + operator does double duty as both an arithmetic and a concatenation operator but it works differently from the & in how it handles nulls. You can use this difference to your advantage when you are concatenating multiple strings such as First middle and last names. If you simply use:

First & " " & Middle & " " & Last

You will end up with two spaces between first and last whenever a person has no middle name. Using the expression shown by arnel which uses both + and & you can avoid introducing the extra spaces. Examine this statement closely to understand how it works. This is much more complicated to accomplish in other languages where + is the only option. You have to use a much more complicated statement with the IIf() to avoid the extra spaces.
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 04-14-2018 at 07:10 PM.
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
lhooker (04-16-2018)
Old 04-16-2018, 10:27 AM   #6
lhooker
Newly Registered User
 
Join Date: Dec 2005
Posts: 274
Thanks: 51
Thanked 1 Time in 1 Post
lhooker is on a distinguished road
Re: Concatenation Not Working

BigHappyDaddy/Ridders/Arnelgp/Pat Hartman,

Thanks to all ! ! ! I used Arnelgp's solution.

lhooker is offline   Reply With Quote
Reply

Tags
concatenation

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenation Help access09 Modules & VBA 10 07-09-2009 11:43 PM
Concatenation AndyShuter General 1 12-20-2005 03:04 AM
self concatenation mohammadagul Forms 3 03-27-2005 11:27 AM
Concatenation I think? Oscar Queries 4 03-28-2003 01:16 AM
[SOLVED] Concatenation Mark E Queries 2 10-07-2001 05:26 AM




All times are GMT -8. The time now is 11:17 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World