Concatenate a name with a yes/no field (1 Viewer)

Metric

Registered User.
Local time
Today, 01:43
Joined
Sep 14, 2019
Messages
26
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!
 

June7

AWF VIP
Local time
Today, 00:43
Joined
Mar 9, 2014
Messages
5,423
[FirstName] & " " & [LastName] & IIf([Leader], ", Leader", "")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:43
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! Does this mean you have other checkboxes for different titles?
 

Metric

Registered User.
Local time
Today, 01:43
Joined
Sep 14, 2019
Messages
26
[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

Registered User.
Local time
Today, 01:43
Joined
Sep 14, 2019
Messages
26
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:43
Joined
Oct 29, 2018
Messages
21,357
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.
 

Metric

Registered User.
Local time
Today, 01:43
Joined
Sep 14, 2019
Messages
26
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!
 

June7

AWF VIP
Local time
Today, 00:43
Joined
Mar 9, 2014
Messages
5,423
Why? That is duplication. You can always concatenate in query or textbox when needed.

Requires code (macro or VBA) to save calculated data.
 

Metric

Registered User.
Local time
Today, 01:43
Joined
Sep 14, 2019
Messages
26
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 I’d like to have the actual name we’re using for the person in there visible with everything else. I’m realizing that I should maybe just build a macro to create a report instead and show everything together there.
 

June7

AWF VIP
Local time
Today, 00:43
Joined
Mar 9, 2014
Messages
5,423
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.
 

moke123

AWF VIP
Local time
Today, 04:43
Joined
Jan 11, 2013
Messages
3,849
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.
 

Users who are viewing this thread

Top Bottom