Here's my query so far:

Code:
SELECT 
	ISNULL(tblHealthCareProviders.HCPPrefix, '') + 
	 tblHealthCareProviders.HCPFName + 
	 ' ' +
	 tblHealthCareProviders.HCPLName + 
	 ISNULL(tblHealthCareProviders.HCPSuffix, ''), etc
What I am doing is creating a single name field out of four possible data in a table: prefix (Dr., Mrs., etc) first name, last name and suffix (Jr., Sr., etc). This query is too simple because what I need to do is: if there is a suffix, append a comma and a space to last name and then append the suffix, otherwise just leave last name alone.

So I would get John Berry, Jr. instead of John BerryJr which is what the query above is giving me.

Do I have to break this out and code something more complex than ISNULL can handle?

Thanks.