Concatenating Fields based on Criteria?

burnout

Registered User.
Local time
Yesterday, 21:44
Joined
Jan 26, 2006
Messages
17
I have a query that is set up to join two name fields...for example:

[ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]

The issue is that this is perfect for names that are entered in the table like this:

Lstnam: ABC HOSP Fstnam: ITAL

They are entered like that for groups, but when individual doctors are entered, the data entry folks are entering them like this:

Lstnam: SMITH Fstnam: BOB Titlcd: MD

So when I join them the first displays correctly as ABC HOSPITAL, the second displays as SMITHBOBMD and I have to manually go in and add a comma.

Is there a way to do an if/then statement or something to tell the query to display results If the titlecd is NOT NULL, display as [ELIGVENDORS.FSTNAM] & [ELIGVENDORS.LSTNAM]&", "&[ELIGVENDORS.TITLCD], else display as [ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]

I'm sure there is, I am new to IF/THEN statments though and was hoping to get some help setting it up.

Thanks in advance and I hope my description is clear enough.

Han
 
Give this a try.

Code:
SELECT IIF(ELIGVENDORS.TITLCD IS NULL, [ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM], [ELIGVENDORS.FSTNAM] & [ELIGVENDORS.LSTNAM]&", "&[ELIGVENDORS.TITLCD]) AS [Full Name], ELIGVENDORS.YourOtherFields
FROM...
 
Thank you! This works perfectly!
 

Users who are viewing this thread

Back
Top Bottom