Concatenate fields

[#1]

Hi All I need to Concatenate the below fields and create one field but in here we are not having Middle name fro all the name and now if middle name doe not exist i need to show it in this way

F_Name, L_Name by excluding the middlename field 

by using the below script it is showing like this

F_Name,  , L_Name if middle name doe not exist.

[F_Name]&', '& M_Name &', '& [L_Name] AS [Total Name]

can anyone help me in excluding the '', ,'' when there is no middle name.

[Anonymous]

Hi,

you can use If statment, IF Middlename is null (or empty) you concatenate only Firstname and Lastname else you concatenate Firstname, Middlename (with commas) and Lastname.

[#3]

also need to have the first letter in first name in uppercase and rest of them in lower case

similarly for the Middle name and Last name

[krishna_2644]

Hi Chinna,

Try

[F_Name] &', '& If(len(trim(M_Name))>0,M_Name,' ') &', '& [L_Name] AS [Total Name]

Thanks

krishna

[#5]

Can you give me the script I am unable to build it

[krishna_2644]

Try

Capitalise([F_Name]) &', '& If(len(trim(M_Name))>0,Capitalise(M_Name),' ') &', '& Capitalise([L_Name]) AS [Total Name]

[sinanozdemir]

How about something like this:

=If(M_Name <> '', F_Name & ', ' & L_Name, F_Name & ', ' & M_Name & ', ' L_Name)

[sunny_talwar · Accepted solution]

for use capitalize() function for that

May be like this:

Capitalized(F_Name) & If(Len(Trim(M_Name)) <> 0, ', ' & M_Name) & ', ' & L_Name as [Total Name]

[Anonymous]

Hi,

sorry, other have do this before me! 🙂

You can use capitalize, if the text contain other words that you dont want to capitalize you can use left+mid and concatenate:

upper(left(firstname,1))&mid(firstname,2,len(firstname)-1)

Regards.

[#10]

thank you all for the wonderful help!

really appreciate your help...........