Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2019, 10:53 PM   #1
Metric
Newly Registered User
 
Join Date: Sep 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Metric is on a distinguished road
Concatenate a name with a yes/no field

Hello,

I am relatively new to Access and just figuring out a lot of this. I am using a query to concatenate first and last names for a group of people. I have had no trouble doing this, but now I’d like to add a title to some people’s names if they possess that title. I have a yes/no field for the title but I don’t know how to include that in the concatenation. So, for example, if the checkbox in the column ‘Leader’ is checked, I’d like the name to be FirstName LastName, Leader. But if it is unchecked, I’d like it be just FirstName LastName. I know this needs an IIF statement, but I just can’t seem to create a formula that works. Can anyone suggest a solution to this? Thank you so much!

Metric is offline   Reply With Quote
Old 09-13-2019, 10:59 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,428
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Concatenate a name with a yes/no field

[FirstName] & " " & [LastName] & IIf([Leader], ", Leader", "")
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-14-2019, 05:37 AM   #3
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,923
Thanks: 57
Thanked 1,296 Times in 1,277 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Concatenate a name with a yes/no field

Hi. Welcome to AWF! Does this mean you have other checkboxes for different titles?

__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
Old 09-16-2019, 11:04 AM   #4
Metric
Newly Registered User
 
Join Date: Sep 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Metric is on a distinguished road
Re: Concatenate a name with a yes/no field

Quote:
Originally Posted by June7 View Post
[FirstName] & " " & [LastName] & IIf([Leader], ", Leader", "")

Thank you so much, this worked like a charm! I think I was overcomplicating this so much trying to do it myself.
Metric is offline   Reply With Quote
Old 09-16-2019, 11:04 AM   #5
Metric
Newly Registered User
 
Join Date: Sep 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Metric is on a distinguished road
Re: Concatenate a name with a yes/no field

Quote:
Originally Posted by theDBguy View Post
Hi. Welcome to AWF! Does this mean you have other checkboxes for different titles?

I don't currently, but I am still very much working out what I'm doing with this database. I might end up with more info in checkbox form.
Metric is offline   Reply With Quote
Old 09-16-2019, 11:13 AM   #6
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,923
Thanks: 57
Thanked 1,296 Times in 1,277 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Concatenate a name with a yes/no field

Quote:
Originally Posted by Metric View Post
I don't currently, but I am still very much working out what I'm doing with this database. I might end up with more info in checkbox form.
Hi. Glad to hear you got it sorted out for now. Good luck with your project. Please don't hesitate to come back if you get stuck.
__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
Old 09-16-2019, 04:14 PM   #7
Metric
Newly Registered User
 
Join Date: Sep 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Metric is on a distinguished road
Re: Concatenate a name with a yes/no field

Quote:
Originally Posted by theDBguy View Post
Hi. Glad to hear you got it sorted out for now. Good luck with your project. Please don't hesitate to come back if you get stuck.

Thanks, you are so welcoming!


I actually already have another question. I'd like to insert the new concatenated name into an existing table so I can use it going forward. I'm not at all sure how to do this!

Metric is offline   Reply With Quote
Old 09-16-2019, 06:22 PM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,428
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Concatenate a name with a yes/no field

Why? That is duplication. You can always concatenate in query or textbox when needed.

Requires code (macro or VBA) to save calculated data.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-16-2019, 10:54 PM   #9
Metric
Newly Registered User
 
Join Date: Sep 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Metric is on a distinguished road
Re: Concatenate a name with a yes/no field

Quote:
Originally Posted by June7 View Post
Why? That is duplication. You can always concatenate in query or textbox when needed.

Requires code (macro or VBA) to save calculated data.
I guess this is a symptom of my novice status, I am probably doing things wrong. I have a table with other information in it about each individual and Id like to have the actual name were using for the person in there visible with everything else. Im realizing that I should maybe just build a macro to create a report instead and show everything together there.
Metric is offline   Reply With Quote
Old 09-16-2019, 11:02 PM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,428
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Concatenate a name with a yes/no field

Users should not interact directly with tables and queries, only forms and reports.

Build queries to retrieve related data then bind form or report to query. Can even build query statements directly in RecordSource property of forms and reports and RowSource of combobox and listbox.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-17-2019, 03:05 AM   #11
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 765
Thanks: 3
Thanked 232 Times in 218 Posts
moke123 will become famous soon enough
Re: Concatenate a name with a yes/no field

Not to muddy the waters but to offer another method of concatenation is the law of propagating nulls. LOPN basically says that anything plus a null is null.

If you had fields - FirstName, MiddleName, LastName, Suffix.
You may not have records with middle names or suffixes like Sr. or Jr.

Code:
 (FirstName + " ") & (MiddleName + " ") & (LastName) & (" " + Suffix)
Note the use of "+" rather than "&" within the parenthesis.
If there is a middle name it will include the middle name with a space after it. If the middle name field is null it wont be included in the concatenation. (null + " ")= null

For your purposes you could use (" " + Title) and instead of check boxes use a combo box to select titles. That way if titles are added or changed you will not have to re-write your forms.

moke123 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate entries from a single field based on another field scoobyduben Forms 5 04-16-2013 09:45 AM
Concatenate 2 field in a control source and sort by one field MrTickle Reports 3 10-05-2012 02:21 AM
concatenate records in the same field SpiritedAway Queries 2 11-18-2011 03:00 AM
How concatenate into one field? SteveL Modules & VBA 12 09-15-2008 07:09 PM
concatenate text field and money field biskra Reports 4 05-22-2004 09:41 PM




All times are GMT -8. The time now is 09:00 AM.


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

Featured Forum post


Sponsored Links


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