Conditional query concatenation

[IroncladRooster]

I am brand new to access and am writing my first query.

My first column concatenates first name middle initial (if available) and last name. I am using the below expression to return that result.

FullName: [Name_Only]![First Name] & (" "+[Name_Only]![MI]) & (" " & [Name_Only]![Last Name])
(with Name_Only being the name of my table)

That works flawlessly.

In trying to build my next expression I tried to follow the pattern from the expression above but it is not working for me. I am sure I am misunderstanding the basics behind the concatenation process.

What I am trying to express next is a concatenation of multi part job titles. In my table I have a have a field titles "title 1", "title 2", and "title 3". Some people in our organization are just "vice presidents' so they only need one title, but others are Vice President, Managing Director, President of awesomeness. In that case he / she would have Title 1, 2 and 3. I will always need a result from Title 1, but I only need a title 2 if 1 is present and will only need 3 if both 1 and 2 are present.

My concatenation attempt looks like this



FullTitle: [Title, prior exp]![Title 1] & (", "+[Title, prior exp]![Title 2]) & (", "+[Title, prior exp]![Title 3])
(with Title, prior exp being the name of my table)

However this spits out Title 1, Title 2, Title 3 and keeps the commas and extra spaces in even when there is no second or third title. It seems like the + operator isn't picking up the null value and is still returning the ", " by itself.
I have results that look like this (note the red commas at the end).
Vice President, Managing Director, President of awesomeness
Vice President, ,
Managing Director, President of Semi-awesomeness,

I'm sure there is a pretty simple fix. Thanks for your help. I look forward to bugging you guys with lots of other equally simple questions in the days and months ahead

[ssanfu]

I would suggest renaming your tables. Names should only be letters, numbers and the underscore. NO spaces or punctuation.

[Title, prior exp] <= no commas

[IroncladRooster]

I would suggest renaming your tables. Names should only be letters, numbers and the underscore. NO spaces or punctuation.

[Title, prior exp] <= no commas

Thanks for the suggestion. I tried that, but it didn't fix the problem.

I ended up going with a long If function to solve the problem. I'm all ears to know if there is a better or more efficient way of doing this, but this is what I have so far.

FullTitle: [titlepriorexp]![Title 1] & IIf([titlepriorexp]![Title 2]="","",", " & [titlepriorexp]![Title 2]) & IIf([titlepriorexp]![Title 3]="","",", " & [titlepriorexp]![Title 3])

*note - I renamed my table "titlepriorexp"

[John_G]

Hi -

I think you should be checking for Null instead of the empty string (""). An empty string is not the same as Null, so
the expression [titlepriorexp]![Title 2]="" is False when [titlepriorexp]![Title 2] is Null.

Using the Nz function, try something like this:

[titlepriorexp]![Title 1] & Nz([titlepriorexp]![Title 2],"", ", " & [titlepriorexp]![Title 2]) & Nz([titlepriorexp]![Title 3],"", ", " & [titlepriorexp]![Title 3])

John

[IroncladRooster]

Hi -

I think you should be checking for Null instead of the empty string (""). An empty string is not the same as Null, so
the expression [titlepriorexp]![Title 2]="" is False when [titlepriorexp]![Title 2] is Null.

Using the Nz function, try something like this:

[titlepriorexp]![Title 1] & Nz([titlepriorexp]![Title 2],"", ", " & [titlepriorexp]![Title 2]) & Nz([titlepriorexp]![Title 3],"", ", " & [titlepriorexp]![Title 3])

John

Thanks for the tip John. I just plugged your formula in but got the below error:
"Wrong number of arguments used with function in query expression <and then it lists the formula you built>".

I've been trying to debug it, but haven't had any luck yet. Any ideas?

[John_G]

Oops!

Sorry - yes, I messed that up. Too early on a Monday and not enough coffee yet.

You do need the Iif - try it like this:

FullTitle: [titlepriorexp]![Title 1] & IIf(isnull([titlepriorexp]![Title 2]),"",", " & [titlepriorexp]![Title 2]) & IIf(isnull([titlepriorexp]![Title 3]),"",", " & [titlepriorexp]![Title 3])

Sorry 'bout that!

John

[IroncladRooster]

Oops!

Sorry - yes, I messed that up. Too early on a Monday and not enough coffee yet.

You do need the Iif - try it like this:

FullTitle: [titlepriorexp]![Title 1] & IIf(isnull([titlepriorexp]![Title 2]),"",", " & [titlepriorexp]![Title 2]) & IIf(isnull([titlepriorexp]![Title 3]),"",", " & [titlepriorexp]![Title 3])

Sorry 'bout that!

John

Thanks for your help John. Works great.