Identify " " in String

[jgelpi16]

Similar to my previous post I have the same field with "LastName FirstName MI". What I need to do is split that string up into three fields, LastName, FirstName and MI. I'm aware of the Mid() function and such, but that won't work because the names can obviously vary in length. Any suggestions?

[RuralGuy]

Why not use a Split() function into an array using the " " as the separator.

[jgelpi16]

That is a good question! Thank you.

[jgelpi16]

RuralGuy...I'm not sure the split function will work for what I am looking to do. I have a table with roughly 460k records in it. Each name needs to be split out into a FirstName, LastName and MI field. From what I read on the split function it doesn't appear it would be efficient for what I am doing. It is quite possible I am just not understanding the way the split() function works.

[pkstormy]

One of these examples is a basic example which takes a full name field and breaks it into first/last name. You can possibly use it to expand upon in your problem. It uses the instr function to find the space between the first/last name.

I couldn't remember which example does the splitting so I posted both.

Your problem is a little more involved since it involves the a 2nd space field in the name with the MI. In this case, you'd probably want to utilize the mid or somehow find the 2nd space value or see below for a way I do this.

Ideally though, I'd think about putting these into separate fields in the table itself. You'll undboubtly encounter this again in your mdb coding somewhere in the future.

One thing I would do is create 3 new fields in the table (FirstName, LastName, and MI). Then I'd write some queries to grab the last name and cut that out of the fullname field. That then makes it easier to separate the MI from the FirstName. So: Jacobs John A would grab Jacobs and put that in the LastName field in the first pass of query and then delete the word Jacobs from the fullname field. Then it's easier to separate John A and put John in the FirstName field and A in the MI field. This is often what I do when I need to separate a name with multiple spaces. Expect to do a little bit of manual fixing for some names. There really isn't a full-proof method for doing this to take into account all types of name situations. I've seen lots of coding which comes close but none that works absolutely flawlessly. A name such as: Jacobs Jones Billy Rae J. (aka: FirstName Billy Rae, LastName Jacobs Jones, and MI J.) makes it challenging.

[NoellaG]

Hi,

be carefull when splitting names based on spaces. A lot of last names have spaces in them (Mac Millan, De Vere, Van Helsink,...)

greetings
NG

[jgelpi16]

Thanks for all your help and advice. I wish the names were just split up to begin with! Unfortunately I wasn't the developer of that piece.

[pkstormy]

Hi,

be carefull when splitting names based on spaces. A lot of last names have spaces in them (Mac Millan, De Vere, Van Helsink,...)

greetings
NG

A great point (and a close name to one of my all time favorite movies - Van Helsing)