Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    113

    Unanswered: Left and Right function

    Hey Guys. I have a field called name that contains the the fullname. How do i separate the name into first , middle and last in an sql statement? here are some different examples:

    Richard M.Rewser Jr.
    Laura L.San
    William M.Mackian
    Jon P.Gaynor II

    Thanks guys

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would create functions that return the FirstName, MiddleName, LastName and Suffix. You can use the Split function to separate each part of the name separated by a space.

    But usually splitting names is not that easy. Do you ever have instances where there is no middle name? What about latin names that may be four or more names?

    I personally acquired software that recognized several name formats and use it to split the name for me. If you are interested I can give you the company's details.

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Have a try of this

    placed in a module

    and will need 4 fields in one table in total, these for this example would be

    Wholename
    Firstname
    middle
    lastname

    gareth
    -----------------------------------------

    Dim txtfield As String
    Dim txtField1 As String
    Dim txtField2 As String
    Dim txtField3 As String

    Set db = CurrentDb
    Set rst1 = db.OpenRecordset("table3")
    rst1.MoveFirst
    Do While Not rst1.EOF
    txtfield = rst1!WholeName
    txtField1 = Left(txtfield, InStr(txtfield, " ") - 1)
    txtfield = Mid(txtfield, InStr(txtfield, " ") + 1)
    txtField3 = Mid(txtfield, InStr(txtfield, " ") + 1)
    txtfield = Mid(txtfield, 1, InStr(txtfield, " "))
    txtField2 = txtfield
    rst1.Edit
    rst1!FirstName = txtField1
    rst1!Middle = txtField2
    rst1!LastName = txtField3
    rst1.Update
    rst1.MoveNext
    Loop
    Set rst1 = Nothing
    Set db = Nothing

    End Sub

  4. #4
    Join Date
    Sep 2004
    Posts
    113

    how

    thanks guys bvut how can i do this in excel? can i use the same module in a macro??

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by inho78
    thanks guys bvut how can i do this in excel? can i use the same module in a macro??
    Uhhh... What are you doing that you're using SQL with excel? Me thinx we're missing seriously critical facts...
    oh yeah... documentation... I have heard of that.

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

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by inho78
    Hey Guys. I have a field called name that contains the the fullname. How do i separate the name into first , middle and last in an sql statement? here are some different examples:

    Richard M.Rewser Jr.
    Laura L.San
    William M.Mackian
    Jon P.Gaynor II

    Thanks guys
    Here I a Module that I Found on the web that Look @ the words in a string
    I use it in query well any where I Can

    '===============================================
    'Words.bas - string handling functions for words
    'Author: Evan Sims [esims@arcola-il.com]
    'Based on a module by Kevin O'Brien
    'Version - 1.2 (Sept. 1996 - Dec 1999)
    '
    'These functions deal with "words".
    'Words = blank-delimited strings
    'Blank = any combination of one or more spaces,
    ' tabs, line feeds, or carriage returns.
    '
    'Examples:
    ' word("find 3 in here", 3) = "in" 3rd word
    ' words("find 3 in here") = 4 number of words
    ' split("here's /s more", "/s") = "more" Returns words after split identifier (/s)
    ' delWord("find 3 in here", 1, 2) = "in here" delete 2 words, start at 1
    ' midWord("find 3 in here", 1, 2) = "find 3" return 2 words, start at 1
    ' wordPos("find 3 in here", "in") = 3 word-number of "in"
    ' wordCount("find 3 in here", "in") = 1 occurrences of word "in"
    ' wordIndex("find 3 in here", "in") = 8 position of "in"
    ' wordIndex("find 3 in here", 3) = 8 position of 3rd word
    ' wordIndex("find 3 in here", "3") = 6 position of "3"
    'wordLength("find 3 in here", 3) = 2 length of 3rd word
    '
    'Difference between Instr() and wordIndex():
    ' InStr("find 3 in here", "in") = 2
    ' wordIndex("find 3 in here", "in") = 8
    '
    ' InStr("find 3 in here", "her") = 11
    ' wordIndex("find 3 in here", "her") = 0
    '===============================================
    Attached Files Attached Files
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    yes teddy me thinks the same!!!

    the answer to the excel question is NO!

    Its requires another format to do the same

    I have reason to believe that there are answers to this question in the excel forums around and about


    Gareth

Posting Permissions

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