Removing extra spaces from the middle of a text field

[Amy E. Baggott]

I have a text field on my report that consists of [FirstName]&" "&[MI]&"
"&[LastName]. However, if the person does not have a middle initial, it
comes out with two spaces between the first and last names (e.g. "Fred
Glutz" instead of "Fred Glutz"). Is there any way in Access to remove the
extra spaces in the field result or am I stuck with it?

 

[Rick B]

The "+" will only add if both fields contain a value.

So...

[MI] + " " would only print the space if there was an entry in [MI].

*** I don't think you need the parenthesis in the following. You can try it
without them if you want...

[FirstName] & " " & ([MI] + " ") & [LastName].

 

[fredg]

I have a text field on my report that consists of [FirstName]&" "&[MI]&"
"&[LastName]. However, if the person does not have a middle initial, it
comes out with two spaces between the first and last names (e.g. "Fred
Glutz" instead of "Fred Glutz"). Is there any way in Access to remove the
extra spaces in the field result or am I stuck with it?

Use the following expression instead of your current one:

=[First Name] & (" "+[MI]) & " " & [LastName]

 

[Amy E. Baggott]

Thank you! It worked beautifully!
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

The "+" will only add if both fields contain a value.

So...

[MI] + " " would only print the space if there was an entry in [MI].

*** I don't think you need the parenthesis in the following. You can try it
without them if you want...

[FirstName] & " " & ([MI] + " ") & [LastName].

--
Rick B

I have a text field on my report that consists of [FirstName]&" "&[MI]&"
"&[LastName]. However, if the person does not have a middle initial, it
comes out with two spaces between the first and last names (e.g. "Fred
Glutz" instead of "Fred Glutz"). Is there any way in Access to remove the
extra spaces in the field result or am I stuck with it?