Removing extra spaces from the middle of a text field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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].
 
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?
Click to expand...

Use the following expression instead of your current one:

=[First Name] & (" "+[MI]) & " " & [LastName]
 
Thank you! It worked beautifully!
--
Amy E. Baggott

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


Rick B said:
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



Amy E. Baggott said:
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?
Click to expand...
Click to expand...
 
Back
Top