Novice needs help ...finding Last Name

I am brand new to VB. I have a simple MS Access 2000 application.
I currenly have no functions or subs.

I need a function to find the last name in a name field.

My input string is in the following sequence. First Name, Middle Name or initial, Last Name and generational title. There are no validations done to validate the sequence. Hence multiple formats are present

examples:

MIKE A BATES JR
MIKE ADAM BATES JR
M A BATES
MIKE A BATES
MIKE BATES JR
MIKE A BATES, JR
MIKE BATES, JR

Of course I am looking for a string that has BATES in it.

You can probably come up with more combinations.

This is a problem that I am sure has been solved many times before.

I would appreciate any help anyone can provide on this.

Thanks in advance.

Comments

  • [b][red]This message was edited by aatkbd at 2005-10-4 11:27:34[/red][/b][hr]
    without doing the coding for you, here is what you need to implement

    you need a list of suffixes you think are used in the database.

    use the split function to return an array of words based on your input string

    Starting with the last element in the array compare it with your list of suffixes. if the current element is not found in the list then you have found the last name.

    This might not work 100% of the time but you should be able to refine it and get it close.

    If you need the code just let me know.

    : I am brand new to VB. I have a simple MS Access 2000 application.
    : I currenly have no functions or subs.
    :
    : I need a function to find the last name in a name field.
    :
    : My input string is in the following sequence. First Name, Middle Name or initial, Last Name and generational title. There are no validations done to validate the sequence. Hence multiple formats are present
    :
    : examples:
    :
    : MIKE A BATES JR
    : MIKE ADAM BATES JR
    : M A BATES
    : MIKE A BATES
    : MIKE BATES JR
    : MIKE A BATES, JR
    : MIKE BATES, JR
    :
    : Of course I am looking for a string that has BATES in it.
    :
    : You can probably come up with more combinations.
    :
    : This is a problem that I am sure has been solved many times before.
    :
    : I would appreciate any help anyone can provide on this.
    :
    : Thanks in advance.
    :



  • Ok, I think you want to check out the Instr() function. It will return an integer value if the search string was found of the location where it was found. Anyways I hope this is quite a bit easier and what you were looking for.
  • : Ok, I think you want to check out the Instr() function. It will return an integer value if the search string was found of the location where it was found. Anyways I hope this is quite a bit easier and what you were looking for.
    :


    Instr will not work unless you all ready know the last name.
  • : : Ok, I think you want to check out the Instr() function. It will return an integer value if the search string was found of the location where it was found. Anyways I hope this is quite a bit easier and what you were looking for.
    : :
    :
    :
    : Instr will not work unless you all ready know the last name.
    :
    Use Split() space as delimeter
    [code]
    Dim MyString As String, strName As String
    Dim MyArray() As String
    Dim i As Integer

    MyString = "Sam M Somebody"

    MyArray = Split(MyString, " ")

    Do Until i = UBound(MyArray) + 1
    strName = MyArray(i)
    i = i + 1
    Loop
    Text1 = strName
    [/code]
  • Thanks for all your replies.

    At the risk of looking like a rookie. I am trying to work off of
    aatkbd's suggestion. This is the sample code that
    I have been trying to make work.

    _____________

    Sub findlastname()
    Dim intNumberofwords As Integer
    LastNameArray = Split("MIKE A. BATES JR", " ")
    intNumberofwords = UBound(LastNameArray)
    If LastNameArray(intNumberofwords) = "JR" Then
    lastname = LastNameArray(intNumberofwords - 1)
    Else
    lastname = LastNameArray(intNumberofwords)
    End If
    End Sub

    ______________________

    I am a rookie. I can not even test this!!! I do not know how to hook this into my "application". My application is a downloaded file from a linux system that I convert to an access table. I have several access reports that I run again this table and a couple of other reference tables. I beleive I want a new field in my table. I do no not know use this code with what I have.
    Sorry .....thanks again.

    ________________________________________




    : [b][red]This message was edited by aatkbd at 2005-10-4 11:27:34[/red][/b][hr]
    : without doing the coding for you, here is what you need to implement
    :
    : you need a list of suffixes you think are used in the database.
    :
    : use the split function to return an array of words based on your input string
    :
    : Starting with the last element in the array compare it with your list of suffixes. if the current element is not found in the list then you have found the last name.
    :
    : This might not work 100% of the time but you should be able to refine it and get it close.
    :
    : If you need the code just let me know.
    :



  • : Thanks for all your replies.
    :
    : I am a rookie. I can not even test this!!! I do not know how to hook this into my "application". My application is a downloaded file from a linux system that I convert to an access table. I have several access reports that I run again this table and a couple of other reference tables. I beleive I want a new field in my table. I do no not know use this code with what I have.
    : Sorry .....thanks again.
    :
    : ________________________________________
    :

    The following code still needs to be modified to make it work in access. If you can give me some additional information about your access database I can help you make the modifications.

    Is the name of the table always the same?
    What is the name of the field that holds the name?

    Inorder to make this work in access you have to convert this subroutine into a function and then create an update table referencing the function to populate your last_name field.

    [code]
    Public Sub Pull_Last_Name()
    On Error GoTo Err_Handler
    Dim Suffix_list(4) As String
    Dim Name_Array As Variant
    Dim LastName As String
    Dim EndofArray As Integer
    Dim I As Integer
    Dim suffix_found As Boolean
    Dim Item As Variant

    [green]' list of suffixes[/green]
    Suffix_list(0) = "I"
    Suffix_list(1) = "II"
    Suffix_list(2) = "III"
    Suffix_list(3) = "Jr"
    Suffix_list(4) = "Sr"

    Name_Array = Split("MIKE A. BATES JR.", " ") [green]'Test Name[/green]
    EndofArray = UBound(Name_Array)
    [green]'cycle through the Name_Array in reverse[/green]
    For I = EndofArray To 0 Step -1
    [green]'compare the Name_Array to the suffix list[/green]
    For Each Item In Suffix_list
    suffix_found = False
    If Name_Array(I) = Item Or Name_Array(I) = Item & "." Then
    suffix_found = True
    Exit For
    End If
    Next
    [green]'if Name_Array(I) is not a suffix then return last name[/green]
    If Not suffix_found Then
    LastName = Name_Array(I)
    Exit For
    End If
    Next

    MsgBox LastName

    Err_Handler:
    If Err.Number <> 0 Then
    Debug.Print Err.Number, Err.Source, Err.Description
    End If
    End Sub
    [/code]

    Remember this code isn't perfect. The suffix list is not complete; Punctutation could cause problems; and last names could be missing or have a space. This means you will still need to review your table after runing the update query.

  • THanks so much..

    : Is the name of the table always the same? Yes CONWAY DATA
    : What is the name of the field that holds the name?
    The full name is in field NAME. There is a last name column named LAST NAME.

    THANKS.....AGAIN...

    ______________________________________________


    : : Thanks for all your replies.
    : :
    : : I am a rookie. I can not even test this!!! I do not know how to hook this into my "application". My application is a downloaded file from a linux system that I convert to an access table. I have several access reports that I run again this table and a couple of other reference tables. I beleive I want a new field in my table. I do no not know use this code with what I have.
    : : Sorry .....thanks again.
    : :
    : : ________________________________________
    : :
    :
    : The following code still needs to be modified to make it work in access. If you can give me some additional information about your access database I can help you make the modifications.
    :
    : Is the name of the table always the same?
    : What is the name of the field that holds the name?
    :
    : Inorder to make this work in access you have to convert this subroutine into a function and then create an update table referencing the function to populate your last_name field.
    :
    : [code]
    : Public Sub Pull_Last_Name()
    : On Error GoTo Err_Handler
    : Dim Suffix_list(4) As String
    : Dim Name_Array As Variant
    : Dim LastName As String
    : Dim EndofArray As Integer
    : Dim I As Integer
    : Dim suffix_found As Boolean
    : Dim Item As Variant
    :
    : [green]' list of suffixes[/green]
    : Suffix_list(0) = "I"
    : Suffix_list(1) = "II"
    : Suffix_list(2) = "III"
    : Suffix_list(3) = "Jr"
    : Suffix_list(4) = "Sr"
    :
    : Name_Array = Split("MIKE A. BATES JR.", " ") [green]'Test Name[/green]
    : EndofArray = UBound(Name_Array)
    : [green]'cycle through the Name_Array in reverse[/green]
    : For I = EndofArray To 0 Step -1
    : [green]'compare the Name_Array to the suffix list[/green]
    : For Each Item In Suffix_list
    : suffix_found = False
    : If Name_Array(I) = Item Or Name_Array(I) = Item & "." Then
    : suffix_found = True
    : Exit For
    : End If
    : Next
    : [green]'if Name_Array(I) is not a suffix then return last name[/green]
    : If Not suffix_found Then
    : LastName = Name_Array(I)
    : Exit For
    : End If
    : Next
    :
    : MsgBox LastName
    :
    : Err_Handler:
    : If Err.Number <> 0 Then
    : Debug.Print Err.Number, Err.Source, Err.Description
    : End If
    : End Sub
    : [/code]
    :
    : Remember this code isn't perfect. The suffix list is not complete; Punctutation could cause problems; and last names could be missing or have a space. This means you will still need to review your table after runing the update query.
    :

  • :
    : THanks so much..
    :
    : : Is the name of the table always the same? Yes CONWAY DATA
    : : What is the name of the field that holds the name?
    : The full name is in field NAME. There is a last name column named LAST NAME.
    :
    : THANKS.....AGAIN...
    : _________________________________________________________

    Put this code in a module and call it whatever

    [Code]
    Public Function Pull_Last_Name(strName as String) as String
    On Error GoTo Err_Handler
    Dim Suffix_list(4) As String
    Dim Name_Array As Variant
    Dim LastName As String
    Dim EndofArray As Integer
    Dim I As Integer
    Dim suffix_found As Boolean
    Dim Item As Variant

    ' list of suffixes
    Suffix_list(0) = "I"
    Suffix_list(1) = "II"
    Suffix_list(2) = "III"
    Suffix_list(3) = "Jr"
    Suffix_list(4) = "Sr"

    Pull_Last_Name = Null
    Name_Array = Split(strName , " ") 'split Name into an array
    EndofArray = UBound(Name_Array)
    'cycle through the Name_Array in reverse
    For I = EndofArray To 0 Step -1
    'compare the Name_Array to the suffix list
    For Each Item In Suffix_list
    suffix_found = False
    If Name_Array(I) = Item Or Name_Array(I) = Item & "." Then
    suffix_found = True
    Exit For
    End If
    Next
    'if Name_Array(I) is not a suffix then return last name
    If Not suffix_found Then
    Pull_Last_Name = Name_Array(I)
    Exit For
    End If
    Next

    MsgBox LastName

    Err_Handler:
    If Err.Number <> 0 Then
    Debug.Print Err.Number, Err.Source, Err.Description
    Pull_Last_Name = NULL
    End If
    End Function
    [/Code]

    Now create a update query.
    Bring in your table and in the first column drag the LAST NAME field down to it and update it to Pull_Last_Name([NAME]).

    Save the query under whatever name and then all you have to do is double click on the query icon to populate the last name. Remember to check your table afterwards to see if the last name is correct and if you find another suffix that is not in the list you should be able to added to the suffix list array.
  • Dear aatkbd,

    I was able to make your suggestion work. Thank you for all your help.

    I did have to take out several statements to get it to run. I am not complaining, just giving feedback, infact I am assuming the changes I made were because I was doing something wrong.

    the lines I removed are:

    On Error GoTo Err_Handler ...looked like it was always going
    to err_handler.
    Pull_Last_Name = Null got invalid use of null (or something like
    that).
    MsgBox LastName got an "Ok" box, that I had to check for
    every record
    Pull_Last_Name = NULL 2nd one - in Err_Handler

    I was able to a DR to the list of suffix.

    Thanks for all your help. I have what I need and you do not need to add any more.... ASH49

    ___________________



    : : THanks so much..
    : :
    : : : Is the name of the table always the same? Yes CONWAY DATA
    : : : What is the name of the field that holds the name?
    : : The full name is in field NAME. There is a last name column named LAST NAME.
    : :
    : : THANKS.....AGAIN...
    : : _________________________________________________________
    :
    : Put this code in a module and call it whatever
    :
    : [Code]
    : Public Function Pull_Last_Name(strName as String) as String
    : On Error GoTo Err_Handler
    : Dim Suffix_list(4) As String
    : Dim Name_Array As Variant
    : Dim LastName As String
    : Dim EndofArray As Integer
    : Dim I As Integer
    : Dim suffix_found As Boolean
    : Dim Item As Variant
    :
    : ' list of suffixes
    : Suffix_list(0) = "I"
    : Suffix_list(1) = "II"
    : Suffix_list(2) = "III"
    : Suffix_list(3) = "Jr"
    : Suffix_list(4) = "Sr"
    :
    : Pull_Last_Name = Null
    : Name_Array = Split(strName , " ") 'split Name into an array
    : EndofArray = UBound(Name_Array)
    : 'cycle through the Name_Array in reverse
    : For I = EndofArray To 0 Step -1
    : 'compare the Name_Array to the suffix list
    : For Each Item In Suffix_list
    : suffix_found = False
    : If Name_Array(I) = Item Or Name_Array(I) = Item & "." Then
    : suffix_found = True
    : Exit For
    : End If
    : Next
    : 'if Name_Array(I) is not a suffix then return last name
    : If Not suffix_found Then
    : Pull_Last_Name = Name_Array(I)
    : Exit For
    : End If
    : Next
    :
    : MsgBox LastName
    :
    : Err_Handler:
    : If Err.Number <> 0 Then
    : Debug.Print Err.Number, Err.Source, Err.Description
    : Pull_Last_Name = NULL
    : End If
    : End Function
    : [/Code]
    :
    : Now create a update query.
    : Bring in your table and in the first column drag the LAST NAME field down to it and update it to Pull_Last_Name([NAME]).
    :
    : Save the query under whatever name and then all you have to do is double click on the query icon to populate the last name. Remember to check your table afterwards to see if the last name is correct and if you find another suffix that is not in the list you should be able to added to the suffix list array.
    :

  • [blue]Pull_Last_Name should of been set to an empty string ("") instead of null.

    You were right to take the msgbox statement out. I was there for testing purposes.

    Without the err handling statments, the code as a high chance to crash the query, application or computer. Before, the err_handler statements should return nothing if something goes wrong.

    I leave it up to you. Good luck.[/blue]

    : Dear aatkbd,
    :
    : I was able to make your suggestion work. Thank you for all your help.
    :
    : I did have to take out several statements to get it to run. I am not complaining, just giving feedback, infact I am assuming the changes I made were because I was doing something wrong.
    :
    : the lines I removed are:
    :
    : On Error GoTo Err_Handler ...looked like it was always going
    : to err_handler.
    : Pull_Last_Name = Null got invalid use of null (or something like
    : that).
    : MsgBox LastName got an "Ok" box, that I had to check for
    : every record
    : Pull_Last_Name = NULL 2nd one - in Err_Handler
    :
    : I was able to a DR to the list of suffix.
    :
    : Thanks for all your help. I have what I need and you do not need to add any more.... ASH49
    :
    : ___________________
    :
    :
    :
    : : : THanks so much..
    : : :
    : : : : Is the name of the table always the same? Yes CONWAY DATA
    : : : : What is the name of the field that holds the name?
    : : : The full name is in field NAME. There is a last name column named LAST NAME.
    : : :
    : : : THANKS.....AGAIN...
    : : : _________________________________________________________
    : :
    : : Put this code in a module and call it whatever
    : :
    : : [Code]
    : : Public Function Pull_Last_Name(strName as String) as String
    : : On Error GoTo Err_Handler
    : : Dim Suffix_list(5) As String
    : : Dim Name_Array As Variant
    : : Dim LastName As String
    : : Dim EndofArray As Integer
    : : Dim I As Integer
    : : Dim suffix_found As Boolean
    : : Dim Item As Variant
    : :
    : : ' list of suffixes
    : : Suffix_list(0) = "I"
    : : Suffix_list(1) = "II"
    : : Suffix_list(2) = "III"
    : : Suffix_list(3) = "Jr"
    : : Suffix_list(4) = "Sr"
    [red]Suffix_list(5) = "Dr"[/red]
    : :
    : : Pull_Last_Name = [red]""[/red]
    : : Name_Array = Split(strName , " ") 'split Name into an array
    : : EndofArray = UBound(Name_Array)
    : : 'cycle through the Name_Array in reverse
    : : For I = EndofArray To 0 Step -1
    : : 'compare the Name_Array to the suffix list
    : : For Each Item In Suffix_list
    : : suffix_found = False
    : : If Name_Array(I) = Item Or Name_Array(I) = Item & "." Then
    : : suffix_found = True
    : : Exit For
    : : End If
    : : Next
    : : 'if Name_Array(I) is not a suffix then return last name
    : : If Not suffix_found Then
    : : Pull_Last_Name = Name_Array(I)
    : : Exit For
    : : End If
    : : Next
    : :
    : : [green]'MsgBox LastName[/green]
    : :
    : : Err_Handler:
    : : If Err.Number <> 0 Then
    : : Debug.Print Err.Number, Err.Source, Err.Description
    : : Pull_Last_Name = [red]""[/red]
    : : End If
    : : End Function
    : : [/Code]
    : :
    : : Now create a update query.
    : : Bring in your table and in the first column drag the LAST NAME field down to it and update it to Pull_Last_Name([NAME]).
    : :
    : : Save the query under whatever name and then all you have to do is double click on the query icon to populate the last name. Remember to check your table afterwards to see if the last name is correct and if you find another suffix that is not in the list you should be able to added to the suffix list array.
    : :
    :
    :
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories