Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Syntax to Trim extra spaces (2003 SP1)

    Sorry in advance for asking a question that I'm sure has been answered a million times, but after searching the Forums and locating 2 Functions that should trim extra spaces, I'm finding that neither works, so I have to ask again.

    I have a text box called FullName that concatenates 4 name fields:
    FullName = Me!address_title & " " & Me!FirstName & " " & Me!MiddleInitials & " " & Me!name + vbNewLine & _

    You can see where this is going . . . if (eg) the Middle Initial is blank, I end up with 2 spaces, so I need to Trim these. I tried adding Trim to the beginning of the Line and enclosing it in Brackets (in addition to trying the 2 aforementioned Functions), but none of these 3 things I tried worked.

    Can someone tell me where I'm going wrong?

    Thanks, as always, for sharing your genius with us! [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Syntax to Trim extra spaces (2003 SP1)

    You can use + instead of &. A+B (where A and B are strings) will result in null if A or B is null.

    FullName = (Me!address_title+" ") & (Me!FirstName+" ") & (Me!MiddleInitials+" ") & Me!name & vbNewLine

    You can also use Trim like this:

    FullName = Trim(Me!address_title & " ") & Trim(Me!FirstName & " ") & Trim(Me!MiddleInitials & " ") & Me!name & vbNewLine

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Syntax to Trim extra spaces (2003 SP1)

    Thanks again for your super-fast and undoubtedly accurate answer.

    The syntax always trips me up, so you're a brilliant help.

    Best wishes.

    Brian

  4. #4
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Syntax to Trim extra spaces (2003 SP1)

    Another alternative (assuming there aren't any double-spaces you wouldn't want replaced with single-spaces) would be to use the Replace function, like this (after you've first assembled FullName from its components):
    <pre>FullName = Replace(FullName, " ", " ")</pre>


  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Syntax to Trim extra spaces (2003 SP1)

    Interesting! I'll have to remember that (read: add it to the document I have with all my Access 'How To . . .'/Tips.

    Thanks again for all your Help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •