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,468
    Provided Answers: 10
    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

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  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
  •