Cocatenation of strings

[swetha]

I have written a query to concat two strings,



select concat([first_name] & ', ' & [last_name]) as [Employee Name] from [employee_details]

THe above query is returning the result correctly only if both first_name & last_name fields are not empty. If the fields are empty, it is dispalyingcomma (,).

How to avoid that?

Can anyone please help me out from this issue?

Thank you

[Robeen]

Try this:

 
SELECT TableName.Field1, TableName.Field2, IIf(Trim([Field1]) Is Null,"Empty" + ", " + [Field2] , [Field1]+ ", " + [Field2]) AS JoinedString
FROM TableName;

If Field1 is a Null, you will get 'Empty, Field2' in the third field.

Otherwise, you will get 'Field1, Field2'.

You might also run into empty strings but try the above first.

Let me know if that works.