Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Unanswered: Table of Names with different spaces

    Hi, in Access I have a table of names with thousands of records, however there are different spaces between the surname and first names. Some have single space and other double spaces.

    Is it possible to fix this so I end up with only a single space. I am using a 'find as you type' search box based on this field too, so the different spaces tend to muck up the search results because of the varying gaps in the name field. I thought I would ask here first as I would hate to mess up the existing data. The names come into the database in the first place by an import and append query from a .txt file, or they can be manually entered by me.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    REPLACE(myCol, '  ', ' ')
    (Two spaces in the first ' ', one in the second ' ').

    Run as many times as you need (for example on first run three spaces becomes two, second run it becomes one).

    This assumes this is a one off clean up and you will properly verify the data before storing it in future.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by pootle flump
    Code:
    REPLACE(myCol, '  ', ' ')
    (Two spaces in the first ' ', one in the second ' ').

    Run as many times as you need (for example on first run three spaces becomes two, second run it becomes one).

    This assumes this is a one off clean up and you will properly verify the data before storing it in future.
    Thanks for that, I will try it out today. I am also interested in your comment about verifying the data before storing it in he future. I don't know anything about that yet. I should come on here more often, you learn heaps and you're a great help, thank you very much.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's called validation, and it's lovely
    are you ever going to want just the firstname or lastname on their own? If so have you considered storing them in separate fields
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Red face

    Quote Originally Posted by georgev
    It's called validation, and it's lovely
    are you ever going to want just the firstname or lastname on their own? If so have you considered storing them in separate fields
    I would love to validate the input of the name field and I would also love to store the first and last names in different fields, however, I might have some difficulty doing it, or perhaps there is a way that I don't know about that can deal with my issues (I know very little). My data is sourced from several different areas. Isn't it funny how working with one big organization there are different data sources that are not stored the same way. For example one system I use allows for downloading and importing the source data in a .txt file which returns the name field for example; SMITH James Michael (two spaces then one space). Another data source returns the name field; SMITH, James Michael, then if there is a need for me to manually enter a name wouldn't it be too difficult to try to copy and paste into a text box if there was validation on the input. Like if I copied and pasted the comma separated name SMITH, James Michael into a text box that required single spaces between names, would the input be rejected and cause an error?. In the case of the comma separated name being inputed by me I tend just to delete the comma. I end up having to separate the first and last names in my queries because I am always required in the course of my work to do searches in my database on either first or last names. Usually this involves employing wild cards because of not having single last name first name fields like you suggested, and also difficult due to the difference in the spacing. So I do have issues with isolating one persons name in a search query. Finally, I got a little bit ahead of myself when I received the code to REPLACE the spaces, I'm actually not sure how I insert that code and run it.

Posting Permissions

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