Concatenate Name  
Author Message
Studibaker





PostPosted: Tue Jul 17 12:43:04 CDT 2007 Top

SQL Server Developer >> Concatenate Name

I'm using sql 2000 and Access 2003.

I have a Lname, Fname and Init fields. I'm using the following to create a
Name field:

([Lname] + ', ' + [Fname] + ' ' + [Init])

If the employee has an init everthing works fine but if they don't have an
init the name field is blank. I've tried this in a query and I get the same
results.

What do I need to do to get the name to display if they don't have an init?

Thanks,

Paul

SQL Server116  
 
 
Russell





PostPosted: Tue Jul 17 12:43:04 CDT 2007 Top

SQL Server Developer >> Concatenate Name ([Lname] + ', ' + [Fname] + ' ' + COALESCE([Init],''))

RLF



> I'm using sql 2000 and Access 2003.
>
> I have a Lname, Fname and Init fields. I'm using the following to create a
> Name field:
>
> ([Lname] + ', ' + [Fname] + ' ' + [Init])
>
> If the employee has an init everthing works fine but if they don't have an
> init the name field is blank. I've tried this in a query and I get the
> same
> results.
>
> What do I need to do to get the name to display if they don't have an
> init?
>
> Thanks,
>
> Paul


 
 
Tom





PostPosted: Tue Jul 17 12:47:21 CDT 2007 Top

SQL Server Developer >> Concatenate Name Probably the value of Init is NULL (which is different that blank or the
empty string). When you concatenate NULL to anything, you will get NULL.
Try:

([Lname] + ', ' + [Fname] + Coalesce (' ' + [Init], ''))

Tom



> I'm using sql 2000 and Access 2003.
>
> I have a Lname, Fname and Init fields. I'm using the following to create a
> Name field:
>
> ([Lname] + ', ' + [Fname] + ' ' + [Init])
>
> If the employee has an init everthing works fine but if they don't have an
> init the name field is blank. I've tried this in a query and I get the
> same
> results.
>
> What do I need to do to get the name to display if they don't have an
> init?
>
> Thanks,
>
> Paul


 
 
Aaron





PostPosted: Tue Jul 17 12:47:20 CDT 2007 Top

SQL Server Developer >> Concatenate Name If one of the columns is NULL, the whole expression becomes NULL. Try:

COALESCE(Lname, '') + ', ' + COALESCE(FName, '') + ' ' + COALESCE([Init],
'')

Or better yet, let the presentation tier concatenate for you.





> I'm using sql 2000 and Access 2003.
>
> I have a Lname, Fname and Init fields. I'm using the following to create a
> Name field:
>
> ([Lname] + ', ' + [Fname] + ' ' + [Init])
>
> If the employee has an init everthing works fine but if they don't have an
> init the name field is blank. I've tried this in a query and I get the
> same
> results.
>
> What do I need to do to get the name to display if they don't have an
> init?
>
> Thanks,
>
> Paul


 
 
pjscott





PostPosted: Tue Jul 17 13:18:02 CDT 2007 Top

SQL Server Developer >> Concatenate Name Got it to work. Thanks for the quick reples.

Paul




> I'm using sql 2000 and Access 2003.
>
> I have a Lname, Fname and Init fields. I'm using the following to create a
> Name field:
>
> ([Lname] + ', ' + [Fname] + ' ' + [Init])
>
> If the employee has an init everthing works fine but if they don't have an
> init the name field is blank. I've tried this in a query and I get the same
> results.
>
> What do I need to do to get the name to display if they don't have an init?
>
> Thanks,
>
> Paul