Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separating two names into two fields (97, 2000)

    I have a field with two names in it (last then first separated by a space) that I want to separate into two fields. How do I do it? It would be particularly useful if I could show the last string, then everything except the last string. Then somebody like Van der Wall Marie would end up with Marie in the FirstName field and Van der Wall in the LastName field.

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Separating two names into two fields (97, 2000)

    You said a field with two names, but obviously you didn't mean two *words*. Are the names always in last name then first name order? If so, you can search for the last space and show everything up to that as the last name. However, what happens if you have a name like Van der Wall Anne Marie?

    Here's a routine that will extract the last word from a string, in this case the First name, and an example of how to use it for this purpose
    <pre>Public Function ExtractLastValue(ByVal varList As Variant, _
    Optional strSep As String) As Variant
    'created 3/14/2000 by C Foust
    Dim strLast As String
    Dim intBegPos As Integer
    Dim intEndPos As Integer

    If strSep = "" Then
    strSep = ";"
    End If
    intBegPos = 0
    intEndPos = InStr(2, varList, strSep)
    Do
    If intEndPos > 0 Then
    If intBegPos = intEndPos Then
    intBegPos = intEndPos + 1
    Else
    intBegPos = intEndPos
    End If
    End If
    intEndPos = InStr(intBegPos, varList, strSep)
    Loop Until intEndPos = 0
    strLast = Right(varList, Len(varList) - (intBegPos - 1))
    ExtractLastValue = strLast
    End Function</pre>


    <pre>Public Function TestExtractLastName()
    Dim strName As String
    Dim strLastName As String
    Dim strFirstName As String

    strName = "Van der Wall Marie"
    strFirstName = ExtractLastValue(strName, " ")
    strLastName = Left(strName, InStr(strName, strFirstName) - 1)

    TestExtractLastName = strLastName
    End Function</pre>

    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating two names into two fields (97, 2000)

    Thanks, John. I looked at the modules. Since I don't know Visual Basic, they were much too complicated for me.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating two names into two fields (97, 2000)

    The database of names is not very large and only has a couple of exceptions, so if I can separate the two names that appear in all but three records, that would be great and I can do these three manually.

    So to make things simple, I am looking for a simple way of separating two words in a field into two separate fields.

    John

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating two names into two fields (97, 2000)

    I believe Wendell suggested in the last month or two a method that is very simple. Export the data to Excel and choose Data menu then text to columns and follow the wizard, then re-import the data into your database. Quick, simple and little hassle.

    Peter N

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating two names into two fields (97, 2000)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    Hi John

    Go to Google.com and search for neatcode2.mdb

    Look at Module String Manipulation/Parsing to get you started

    http://support.microsoft.com/?kbid=148287

    HTH

    John

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating two names into two fields (97, 2000)

    Thank you for the suggestion. For what I have in mind, it's just the ticket.

Posting Permissions

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