Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separate First & Last Name (2000 / 2002)

    How can I separate a field called Name into 2 fields - first name and last name.
    Thank you in advance.

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

    Re: Separate First & Last Name (2000 / 2002)

    Create a query based on the table with the names. Add two calculated columns:

    FirstName: Left([Name],InStr([Name]," ")-1)

    and

    LastName: Mid([Name],InStr([Name]," ")+1)

    This assumes that ALL names will be of the form "firstname lastname", it will return incorrect results if there are names with middle initials, double names etc.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Separate First & Last Name (2000 / 2002)

    When you have a large data set to process, it might be handy to filter all irregular names (i.e. with more than one space):

    1. Paste & save this function in a module

    Public Function fCountSpaces(strInput As String)
    Dim blnCertainlyNoSpaces As Boolean
    fCountSpaces = 0
    blnCertainlyNoSpaces = False
    Do While blnCertainlyNoSpaces = False
    If Nz(InStr(strInput, " "), 0) = 0 Then
    blnCertainlyNoSpaces = True
    Else
    strInput = Mid(strInput, InStr(strInput, " ") + 1)
    fCountSpaces = fCountSpaces + 1
    End If
    Loop
    End Function

    2. add an additional calculated field in your query like: "Spaces: fCountSpaces([Name])"
    (and add a criterium to it like "<>1" to list them, or "=1" to exclude them)

    p.s. Is there's a chance that there might be leading and trailing spaces spoiling the fun? Then it might be wise to remove those first, by using Trim([Name]) instead of the original field value, in Hans' code as well as in the calculated field above.

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separate First & Last Name (2000 / 2002)

    If this is a one-time process then your easiest way is to copy and paste the data into Excel and use the Data - Text to Columns feature.

    Just a suggestion.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Posting Permissions

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