By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Parse a string to pull second of three words

P: 8
Access 2k3

Query:

I have a string: DOE JOHN MICHAEL (last first middle)
I need to pull JOHN out of it

I can pull out DOE, and get the length of DOE, what I'm having trouble with is how to pull JOHN out. I've been working on it for a while and getting frustrated, so I'm coming to the pros for help.


Here is what I have so far.
Expand|Select|Wrap|Line Numbers
  1. SELECT Left(tblUnit_Data_Current.NAME,InStr(1, tblUnit_Data_Current.NAME," ")-1) as LastName,
  2.  
  3. Len (left(tblUnit_Data_Current.NAME,InStr(1, tblUnit_Data_Current.NAME," ")-1)) as LastNameLength
  4.  
  5. FROM tblUnit_Data_Current;
Any help, guidance or direction is appreciated.

thx

jm420a
Jul 31 '08 #1
Share this Question
Share on Google+
2 Replies


missinglinq
Expert 2.5K+
P: 3,532
For simplicity's sake, I'll call the string NameString:
Expand|Select|Wrap|Line Numbers
  1. Mid(NameString, InStr(NameString, " ") + 1, InStrRev(NameString, " ") - InStr(NameString, " ") - 1)
This has been tested with a variety of first, middle and last names. It does, however, require that the string has at least a middle initial, if not a full middle name.

Welcome to Bytes!

Linq ;0)>
Jul 31 '08 #2

NeoPa
Expert Mod 15k+
P: 31,492
There are other ways (specifically using Split()) if VBA is used. How it is best done does depend on exactly what can be expected from the data and what is expected in the output.

Are the separator characters always spaces for instance?

Is calling a VBA procedure an acceptable solution?
Aug 5 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.