Concatenate Full Name Query problem

[pkeen]

Hi Everyone!

I am designing a database for my own business and have made a copy of it as a template (to start again without my practice data) and now my query doesn't work.

The query (qryClientsExtended) concatenates the first and last names of clients for printing, searching etc. But allowing either the first or last name to be null.

The SQL (I found via tutorial):

SELECT IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])) AS [Client Name], tblClient.*
FROM tblClient

It worked fine on my old database. Like:

Sorry I cant get these illustrations right! But on the second time the [Client Name] field was blank for John (the one with only a first name.

[Client Name] [First] Last
Ben Mead Ben Mead
John John
Clark Clark

But now it will not work for contacts with only first names:

[Client Name] [First] Last
Ben Mead Ben Mead
John
Clark Clark

If anyone knows whats gone wrong here, I'd much appreciate some help. (this is my first post), and i'm pulling my hair out about it.

Thank you ever so much.

Pete

 

[arnelgp]

[FirstName] & Trim(" " & [LastName])

 

[pkeen]

Thanks for the reply.

Yours is an elegant solution although in its current form it leaves no blank space between the names.

By working through the logic of the nested iff statements I have come up with this, which is working for now.

IIf(IsNull([LastName]),[FirstName],IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])) AS [Client Name]

 

[pkeen]

But what interests/frustrates me is why seemingly identical procedures, database structure (everything) can produce different results...

 

[jdraw]

You could try (untested)

SELECT IIf(Len([LastName])=0,FirstName, IIf(Len([FirstName])=0,[LastName],[FirstName] & " " & [LastName])) AS [Client Name]
FROM tblClient

 

[pkeen]

Thanks jdraw, I tested yours and it yielded similar results to my second attempt above.

It seems to follow a similar logic.

 

[jdraw]

What is your table design? Are you really storing 3 fields here?
If so, you do not store [Client Name], you can concatenate First and Last when needed.
Are you trying to cleanse some data?

Please tell us what this means.

[Client Name] [First] Last
Ben Mead Ben Mead
John
Clark Clark

 

[MarkK]

Nulls propagate thru an expression, so Len(Null) will result in a Null, and Null + " " will result in Null, but the '&' operator allows you to concatenate nulls as empty strings. Knowing this you can mix the "+" and "&" operators in a single expression to isolate and remove Nulls with their associated punctuation or spacing.

Try these things in the immediate pane to see how they work . . .

? "FirstName" & Null   [COLOR="Green"]'in this case the null is concatenated as an empty string[/COLOR]
? "FirstName" + Null   [COLOR="Green"]'in this case the whole expression is Null[/COLOR]
? "FirstName" + " " & "MiddleName" + " " & "LastName" [COLOR="Green"]'complex expression using both '+' and '&'[/COLOR]
? Null + " " & Null + " " & "LastName" [COLOR="Green"]'demonstrates how nulls can be isolated in an expression[/COLOR]

See how in the fourth item the Null + " " evaluates to a Null, which removes the space. Then you '&' that result into the rest of your string.
Hope this helps,

 

[arnelgp]

sorry should be:

Trim(([FirstName] + " ") & [LastName])