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: 318
Thanks: 108
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: 203
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
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,209
Thanks: 83
Thanked 1,532 Times in 1,427 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
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

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

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.
,
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


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.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
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
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,592
Thanks: 55
Thanked 2,095 Times in 2,007 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
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 offline   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: 27,336
Thanks: 13
Thanked 1,403 Times in 1,337 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 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: 318
Thanks: 108
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:42 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