Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello. Access 2003.

    I have a field with three names separated by semi-colons. E.g. 'rod;jane;freddy'.
    How can I obtain the second (middle) name in a query?
    Thank you, Andy.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    My suggestion would be to use the Instr() function which will tell you where the first semi-colon is, then do it again using that info to find the second semi-colon. Then you can use the Mid() function to pull that info from the field. And you should be able to do it with a single query if you do the expressions in that order in the columns.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, that's what I'm trying to do - but haven't quite got the expression. Andy.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you post the SQL that you're using at the moment?
    Gre

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Assuming tha there only 3 names then you can use Instr and InstrRev along with MID

    One Counts first ; from left and the Rev takes the one from the right

    Asssuming your field is called txtData (substitute your own)

    Mid([txtData],InStr([txtData],";")+1,InStrRev([txtData],";")-InStr([txtData],";")-1)

    should give the middle word.
    Andrew

Posting Permissions

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