Tidy Text Concatenation (1 Viewer)

[Dmak]

Hello there.

I have a query with 3 text fields; Title, FirstName, Lastname.

I've concatenated them with Fullname: [Title] & " " & [Firstname] & " " & [LastName] so it produced eg Mr John Smith.

Slight problem is there isn't always text in all fields and I get redundant spaces.

Not a major problem but I'd like my db to look smart throughout.

If anyone can let me know how I achieve this it would be greatly appreciated.

Many thanks,

Dan

 

[pr2-eugin]

Hello Dan, This is just my way of doing it.. But you could wait for others to respond, I would create a function to do this..

Fullname: getFullName([Title], [Firstname], [LastName])

Where getFullName is a Function in a Module as,

Public Function getFullName(titleVar As Variant, fNameVar As Variant, lNameVar As Variant) As String
    Dim retStr As String
    If Len(titleVar & vbNullString) <> 0 Then retStr = retStr & titleVar & " "
    If Len(fNameVar & vbNullString) <> 0 Then retStr = retStr & fNameVar & " "
    If Len(lNameVar & vbNullString) <> 0 Then retStr = retStr & lNameVar & " "
    getFullName = Trim(retStr)
End Function

 

[Dmak]

Hello Dan, This is just my way of doing it.. But you could wait for others to respond, I would create a function to do this..
[/CODE]

This is really appreciated, thank you!

 

[GanzPopp]

If you only want to use SQL try the following

TRIM(REPLACE([Title] & " " & [Firstname] & " " & [LastName], "  ", " ")) AS FullName

in your query.

 

[Pat Hartman]

You could also take advantage of the different way that + works when used as a concatenation operator. When you concatenate two fields, one of which is null, you get null returned if you use + but you get the non-null field when you use &. So:
Null + " " results in null But
Null & " " results in a space.

(Title + " ") & (FirstName + " ") & LastName