display nickname in quotes 1

[topwaya]

Hi, I currently have a query that will display the fullname from individual fields.
For instance, it displays:
Doe, John Jr

I would like it to display the nickname in quotes after the first name:

Doe, John "Johnny" Jr

How do I do that?
Here is my current SQL:

SELECT tblPersonal.NameL & "," & tblPersonal.NameF & " " & tblPersonal.SUFFIX AS FullName
FROM tblPersonal
ORDER BY tblPersonal.NameL;

Thanks much!

 

[Golom]

SELECT tblPersonal.NameL & "," & 
       tblPersonal.NameF & 
       " """ & tblPersonal.NickName & """ " &
        tblPersonal.SUFFIX AS FullName
FROM tblPersonal
ORDER BY tblPersonal.NameL;

 

[topwaya]

Thank you, I have the following code:

SELECT tblPersonal.NameL & "," & tblPersonal.NameF & " """ & tblPersonal.NICK & """ " & tblPersonal.SUFFIX AS FullName
FROM tblPersonal
ORDER BY tblPersonal.NameL;

And it displays:

ABEL,TAYLOR ""

the nickname doesn't show up. the NICK field is in the same table, anything I can try? Thanks for your help!

 

[topwaya]

Oops - silly me.

Abel doesn't have a nickname.

How do I get the quotes only if there is a nickname?

Thanks much!

 

[Golom]

SELECT tblPersonal.NameL & "," & 
       tblPersonal.NameF & 
       IIF(NOT IsNull(tblPersonal.NickName),
       " """ & tblPersonal.NickName & """ ", "") &
        tblPersonal.SUFFIX AS FullName
FROM tblPersonal
ORDER BY tblPersonal.NameL;

 

[topwaya]

Thank you,

For some reason I'm getting some displaying correctly (with quotes & nickname or no nickname), and some with just the quotes. Is there anything I can check that would be causing that? The only difference is that the names that don't display correctly are not all caps. I actually don't want the names to be all caps, but some were imported that way.

FullName
Berg,David ""
BERNARD,JOHN
BERRIEN,SAMUEL
BERRY,PATRICK
BERUBE,RONALD
BEST,WILLIAM
BETANCOURT,GUILLERMO "Will

 

[Golom]

You might modify the IIF test to

IIF ( Len(NZ(tblPersonal.NickName,"")) > 0, ...

Could be that you have fields that are a zero-length text field rather than NULL.

 

[PHV]

SELECT tblPersonal.NameL & ',' &
tblPersonal.NameF &
IIf(Trim(tblPersonal.NickName & '')<>'',
' "' & tblPersonal.NickName & '" ', '') &
tblPersonal.SUFFIX AS FullName
FROM tblPersonal
ORDER BY tblPersonal.NameL;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[topwaya]

Works like a charm!!

Thanks so much !!!