Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Exclamation Unanswered: Grab the last name from the full name?

    I have templates that are created in the database and i have many fields on those templates that are getting populated from fields in the database.

    there is a combo box in the database that has a list of names and an option to add a new contact.When users add a new contact there is only one field to enter the first and the last name.

    Now in order to have the salutation in my letters (exear Mr Smith) i need to be able to take only the last name,Title=Mr is a separate field.

    Is there a way to take only the last name even though it is entered in the database as one field.

    Regards

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Post

    If the last name lies always in the second position after title and separated with a space then
    PHP Code:
      function GetLastName(fullName As String )
      
    dim leer as Byte
      leer 
    =instr(1,fullName," ")+1
      lastName
    =  Mid$(fullNameleerlen(fullName)-leer
    To call it
    yourLastName= GetLastName("Mr Impala") it should return Impala

  3. #3
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thanks for your help once again.

    I don't have time now to see if it works, but will send a post back with some remarks.

    I am assuming they enter the first name first and then the second name,but I might be wrong and it is not separated by comma.
    Once entered in the database it gets save exactly like this (Bob Jones)

    And don't worry about " mr" in my post it is just creating confusion it is a different field,have that under control.

    Regards

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    Private Function getLastName(strSource As String) As String
      Return Mid(strSource, InStrRev(strSource, " "))
    End Function
    Last edited by r123456; 07-14-04 at 04:06.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by r123456
    Code:
    Private Function getLastName(strSource As String) As String
      Return Mid(strSource, InStrRev(strSource, " "))
    End Function
    What does InStrRev do? It is not a built in function in Acce$$ 97 perhaps recently in 2000 and heigher

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by r123456
    Code:
    Private Function getLastName(strSource As String) As String
      Return Mid(strSource, InStrRev(strSource, " "))
    End Function
    Hey nice!!

    I usually reverse the string and then pull out the desired characters, didn't realize there was a function that will do it all in one shot.

    One for the file!!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Hi guys, I need your help again, from my question you will be able to see that I am not good with code,but I am slowely learing.

    the field I am trying to grab the last name from is defined in a variable within a function.This is the field "szVariables(61) = "Contact"" and this how I am geting a value for it "szData(61) = GetPersonFields(nLetterTo, "Contact")"

    This is my function for getting the name/field form the table:
    Public Function GetPersonFields(ByVal nID As Integer, ByVal szField As String) As String

    Dim myDB As Database
    Dim myRec As Recordset
    Dim myTable As TableDef

    Set myDB = CurrentDb
    Set myRec = myDB.OpenRecordset("SELECT * FROM [CONTACT DETAILS TABLE] WHERE ID=" & Trim$(Str$(nID)))
    On Error Resume Next
    myRec.MoveFirst

    GetPersonFields = myRec(szField)
    myRec.Close

    End Function


    Now after all this how do I incorporate the code/example you have given me to get the last name only.

    Sorry if I am being a pain....

    Regards

Posting Permissions

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