Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Space after comma (WIN XP/Access 2003)

    I know I have seen this here before, but I cannot find the post.

    Field: Name
    Consists of Last Name,First Name

    The First Name MAY or MAY NOT be preceded by a space.

    EVERY Last Name has no space and a comma.

    I would like to update this field in a query to be identical as follows:

    Last Name, First Name (One space after the comma regardless if there was a space already or not)

    I was able to get as far as putting a space after the comma, but for the records that already had a space,
    they wound up with 2 spaces.

    Thanks for any help or directing me to the post that dealt with this.

    Sincerely,
    Michael

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

    Re: Space after comma (WIN XP/Access 2003)

    Try this expression in the Update to line:
    <code>
    Left([Name], InStr([Name], ",")) & " " & Trim(Mid([Name], InStr([Name], ",") + 1))
    </code>
    The Trim function removes a space before the first name if present.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Space after comma (WIN XP/Access 2003)

    Thank you Hans. I will try it as soon as I get back to my desK !!

    Have a nice weekend !

    Michael

Posting Permissions

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