concat when missing a value 2

[byurow]

hey everyone,

I have a table with the following data:

LastName FirstName MiddleInt
Doe John D
White Karen P
Johnson Mary

(note the third user does not have a middleint)

Now, I want to concat the names into one field so the data looks like so:

Doe, John D.

I have built a query where I use the following code:

LastName + ", " + FirstName + " " + MiddleInt + "." As UserName

The query works great for the names that have middleint's but for the user that does not have a middleint, I get a record, with no value in the UserName column.

Is it possible to concat 3 fields when a peice of data may be missing?

I hope you understand what I am asking!!!!!!!!!!!!

Thanks!

Brenda

 

[byurow]

never mind! I figured it out...I just needed to put nz( ) around the middleint field in my query.

LastName + ", " + FirstName + " " + Nz(MiddleInt + ".&quot As UserName

Sometimes persistance pays off!

Thanks anyway!

 

[Transcend]

Try changing your query to use the Nz function

LastName + ", " + FirstName + " " + Nz(MiddleInt,"&quot + "." As UserName

The Nz function replaces occurrences of null values with a value you choose

Transcend

 

[dhookom]

Leave some "+" and change some to "&":
LastName & ", " & FirstName & " " + MiddleInt + "." As UserName

A + will null adjacent expressions.


Duane
MS Access MVP

 

[Transcend]

Well done

Transcend

 

[byurow]

Thanks Transcend and dhookom!