Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Split Names and Surnames (Access 2000 >)

    To adhere to normalisation, a column of Full Names must be split into Name and Surname. How do you do this in Access, as I am currently exporting the data to Excel and using a function to split Name and Surname, and then importing back into Access.??!!
    Tx
    Regards,
    Rudi

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

    Re: Split Names and Surnames (Access 2000 >)

    It depends on how well-formed the data are. In my experience, there are (almost) always anomalous data, needing manual intervention, and this is easier in Excel than in Access. If ALL names have exactly the same format, you can use an update query and use functions such as Split or a combination of Left, Mid and InStr.

    See the thread starting at <post#=257,644>post 257,644</post: >.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Split Names and Surnames (Access 2000 >)

    I'm surprised that Access does not have functions to do this task. (It would be ideal to prevent import / export of data to do this type of task!!) In Excel, I would use the Search Function to find the first space and use Left(Name, SearchResult - 1) to get the name, and use Mid to get the rest after the position of the space. I see Access does not have Search as a function. Is there something similar with a different name??

    Tx for that thread too.
    Regards,
    Rudi

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

    Re: Split Names and Surnames (Access 2000 >)

    InStr is the function to use. say that the names are in the format "FirstName LastName". You can then use the expression
    <code>
    Trim(Left([FullName],InStr([FullName]," ")))
    </code>
    to return the first name, and
    <code>
    Trim(Mid([FullName],InStr([FullName]," ")))
    </code>
    to return the last name (replace FullName with the name of the full name field).

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Split Names and Surnames (Access 2000 >)

    OK...thanx.

    I also noted that InStr also reads from left to right searching for a " "??

    It works great.
    Regards,
    Rudi

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

    Re: Split Names and Surnames (Access 2000 >)

    InStr is a VBA function, so you can look it up in the VBA help. It can be used to search for any text. There is also a function InStrRev that searches from right to left, but that can't be used in expressions in Access 2000, I think - that became possible in Access 2002.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Split Names and Surnames (Access 2000 >)

    .ofni taht rof xT
    sreehC <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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