Concatenate name fields

[lcrawley]

I'm trying to concatenate separate fields in a query to make an output
file containing a single name record. I've tried using the IIf( etc.
statement, but it doesn't seem to be working and I haven't been able to
find anything on a search.

I have the following fields:

FNAME LNAME MINIT DEGREE1 DEGREE2 DEGREE3 DEGREE4
Craig Smith J MA LCSW CADC
RADC
John Doe MS LPC
Jane Reed T MD

MINIT and DEGREE2, 3 AND 4 may all contain null values. I need to
concatenate this into one field, dropping null values if they exist to
create the following output:

Smith, Craig J, MA LCSW CADC RADC
Doe, John MS LPC
Reed, Jane T, MD

Thanks for any assitance you can offer.

Liz

 

[Guest]

Name and Degree:[LNAME]&", "& [FNAME] & " " &IIF([MINT] Is Null, Null,
[MINIT] &", " & IIF([DEGREE1] Is Null, Null, [DEGREE1] & IIF([DEGREE2] Is
Null, Null, " " &[DEGREE2] IIF([DEGREE3] Is Null, Null, " " &[DEGREE3]
IIF([DEGREE4] Is Null, Null, " " &[DEGREE4]