Concatenate a name with a yes/no field

[Metric]

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]

[FirstName] & " " & [LastName] & IIf([Leader], ", Leader", "")

 

[theDBguy]

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

 

[Metric]

[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]

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 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]

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]

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

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

 

[Metric]

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]

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]

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.

 (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.