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

Lame String Parsing Question

P: n/a
I have a completely lame string parsing question, but I need an answer
fast and I know this is where to get it...I'm not completely familiar
with the Len, Right, Left, and mid functions and I have a field that
looks like Last, First M. I need to parse it out into three
fields...help??

Thanks!
Shelley

Aug 17 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
I have the one table that's Last,First Middle but my other table is
First Middle. Last. I need both to parse into three fields. Help??

thanks,
Shelley

Aug 17 '06 #2

P: n/a
"sbowman" <sh************@carefirst.comwrote in
news:11**********************@75g2000cwc.googlegro ups.com:
I have a completely lame string parsing question, but I need
an answer fast and I know this is where to get it...I'm not
completely familiar with the Len, Right, Left, and mid
functions and I have a field that looks like Last, First M. I
need to parse it out into three fields...help??

Thanks!
Shelley
first thing to do is find the comma.
dim commaposition as integer
commaposition = instr(1,combinedfield,",")
' now split off the lastname part
lastname = left(combinedfield, commaposition - 1
' now take the rest of the field, we'll put it in firstname
firstname = trim(mid(combinedfield, commaposition +1))
'now we need to find the space in firstname,
'write the right side to middleinitial and then
'overwrite the firstname with what's to the left of the space.

I'll leave you to reuse the instr(), mid() and left() functions
to do that. You don't need the len() or right() functions for
this excercise at all.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 17 '06 #3

P: n/a
"sbowman" <sh************@carefirst.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:
I have the one table that's Last,First Middle but my other
table is First Middle. Last. I need both to parse into three
fields. Help??

thanks,
Shelley
See the reply I made to your other post. You'd use the same
techniques.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 17 '06 #4

P: n/a
On 17 Aug 2006 14:27:02 -0700, sbowman wrote:
I have a completely lame string parsing question, but I need an answer
fast and I know this is where to get it...I'm not completely familiar
with the Len, Right, Left, and mid functions and I have a field that
looks like Last, First M. I need to parse it out into three
fields...help??

Thanks!
Shelley
If EVERY record has a
Last Name comma First Name space Middle Initial dot
(and your version of Access supports the InStrRev() function) you can
use:

LastName:Left([CombinedName],InStr([CombinedName],",")-1)

FirstName:
Mid([CombinedNames],InStr([CombinedNames],",")+2,(InStrRev([CombinedNames],"
")-1)-(InStr([CombinedNames],",")+1))

MiddleInitial:Right([CombinedNames],2)

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Aug 17 '06 #5

P: n/a
I'm thinking what I need to do is just parse out the First and Last
names, not all fields contain a middle initial and I'm only matching
records on First Name Last Name anyway. I have the Last name parsed out
easily, but how do I get the first name to exclude the middle initial?

Thanks!
Shelley

Aug 18 '06 #6

P: n/a
I just figured out that my data was imported with a bunch of spaces at
the end of each field, that's why my functions/code weren't
working...phew! Thanks everybody for all of your help!

Shelley

Aug 18 '06 #7

P: n/a

"sbowman" <sh************@carefirst.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
>I have a completely lame string parsing question, but I need an answer
fast and I know this is where to get it...I'm not completely familiar
with the Len, Right, Left, and mid functions and I have a field that
looks like Last, First M. I need to parse it out into three
fields...help??
If it is truly "regular" and closely follows the pattern you give, and you
are using a recent version of Access, you can use the Split function to
split on blank to separate the three pieces. On the other hand, that won't
work if you have names like Von Furstenburg, or De Carlo.

Left("Smith, John A.",Instr("Smith, John A.",",")- 1) will return Smith.

Mid("Smith, John A.", Instr("Smith, John A.",",")+ 2, Len("Smith, John
A.") - Instr("Smith, John A.",",") - 4) will return John.

Left(Right("Smith, John A.",2), 1) will return the A,

If you replace the literal with your variable or field name, these
expressions should do what you want.

Aug 21 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.