Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: queries for splitting text

    Looks like ive hit a homer simpson moment!!!!!!!

    Ive been tasked to put a query in to a premade A2K database that will read atable that the original users has placed the first name and middle name in the same field ie John Robert then suname as ie Jones a different field. Now they want 2 options
    Option 1
    Split first name(s) in to first name and middle name (2 seperate columns as such)

    and then

    more or less the same set up but splits the first name (John) and then the intitial of the secod name (R).

    ive used the following in SQL, it splits the second name just right BUT it drops of the first letter of the first name ie instead of
    JOHN I get OHN and for the life of me i cant see why it works if i place aspace in front of the first name so im a bit stumped

    SELECT IIf(InStr(Mid([first name(s)],InStr([first name(s)],Chr$(44))+2),Chr$(32))>0,Mid(Mid([first name(s)],InStr([first name(s)],Chr$(44))+2),1,InStr(Mid([first name(s)],InStr([first name(s)],Chr$(44))+2),Chr$(32))-1),Mid([first name(s)],InStr([first name(s)],Chr$(44))+2)) AS FirstName, IIf(InStr(Mid([First name(s)],InStr([First name(s)],Chr$(44))+2),Chr$(32))>0,Mid(Mid([First name(s)],InStr([First name(s)],Chr$(44))+2),InStr(Mid([First name(s)],InStr([First name(s)],Chr$(44))+2),Chr$(32))+1),"") AS MiddleName
    FROM learner;


    any ideas????


    regards
    gareth
    Last edited by garethfx; 07-19-04 at 16:44.

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    To extract the last name from a Field with first and last name, or with first, middle and last name (ex. [Field1]), you can use:

    Mid([Field1], InStrRev([Field1], " ", 1))

    To extract the first (and optionally middle name, if present), use):

    Left([Field1], InStrRev([Field1], " ", 1) - 1)

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    This was already asked a few days ago [by me in a sub thread] http://www.dbforums.com/showthread.p...16#post3702616 Look there.
    Ryan
    My Blog

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    OK thanks matthew - tried that but the system cant recognise InStrRev
    if you see my sql version of the query its quiet hardworking in the task. but whilst its nearly there I loose the FIRST letter of the first name so as i said im nearly there but cant quite it the target.

    rguy84 - the issue you had was an EXCEL one and not Access im ok with that but Access 2k needs just a bit more thanks anyway
    regards

    gareth

  5. #5
    Join Date
    Feb 2004
    Posts
    137
    Ahh, in that case, create a new Module, paste the following code into it, and save the Module as modInStrRev:
    Code:
    Public Function InStrRev(ByVal sIn As String, sFind As String, _
        Optional nStart As Long = 1, Optional bCompare As _
        Long = vbBinaryCompare) As Long
    
        Dim nPos As Long, sTmp As String
        For nPos = Len(sIn) To 1 Step -1
            sTmp = sTmp & Mid(sIn, nPos, 1)
        Next
        sIn = sTmp: sTmp = ""
        For nPos = Len(sFind) To 1 Step -1
            sTmp = sTmp & Mid(sFind, nPos, 1)
        Next
        sFind = sTmp: sTmp = ""
        nPos = InStr(nStart, sIn, sFind, bCompare)
        If nPos = 0 Then
            InStrRev = 0
        Else
            InStrRev = Len(sIn) - nPos - Len(sFind) + 2
        End If
    
    End Function

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    just export, fix, re-import by keys
    Ryan
    My Blog

Posting Permissions

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