Breaking up words within a field

  • Thread starter Thread starter Jason Walter
  • Start date Start date
J

Jason Walter

I know this topic has been discussed before, but I have not found an
answer to my question.

I'm not very good at programming Access, although I have managed a few
things with help form this newsgroup members.

I have a field that comes from a linked txt file. It contains a person's
name. The format is Lastname Firstname Middlename SpousefName
SpouseLname Etal

What I need to do is break out the Lastname and firstname. The first 2
names. I need them in seperate fields.
Ken Snell responded once before by saying to use an update query using
the Left, Mid, and Right functions to parse the text strings and to
check in the help file. I still couldn't find an answer.

Can anyone help me.
Jason
 
jason,

You're right. This is a very common question.
Need some help from you, though.
What is the exact order of the names within the string?
What is the name separator?
Are all records in the same order?

If the name is such as
van der Steen Robert
how are we to know what is the last name or the first name?

van der Steen, Robert
makes life a bit simpler, as the comma delimitates the last and first names.

You might want to search http://www.groups.google.com
for "Parse" or "Parsing Names", or "Splitting Strings", etc.

Also take a look at Microsoft KnowledgeBase article:
210588 'ACC2000: How to Parse Comma-Separated Text into Multiple Fields'
 
I'll try to search some more. In the meantime, here is the exact term.
Lastname Firstname Middlename SpousefName SpouseLname Etal

Each is seperated by a space only. All I'm concerned with is taking the
first word and the second word(after the first space) and breaking them
into thier own fields.

Thanks, Jason
 
I have tried this and it was almost successful....
The field with the entire name is Name. I added fields Fname & Lname.
In an update query, I put the following command in the update to field
for Fname: Mid("Name",InStr("Name"," ")+1)

I did not put "" around Name, it added that on it's own. It almost
worked. It put the word, Name in every entry in the Fname column,
instead of breaking out the word in front of the first space.
 
Use square brackets to indicate it's a field name:
Mid([Name],InStr([Name]," ")+1)

In general, by the way, it's a bad idea to name a field "Name", or any
other word that's also the name of a common property or function in
Access (e.g. Date, Month, Year, Height, Width, Value, Text). Sooner or
later it tends to cause confusion, for the programmer or Access or
both<g>.


I have tried this and it was almost successful....
The field with the entire name is Name. I added fields Fname & Lname.
In an update query, I put the following command in the update to field
for Fname: Mid("Name",InStr("Name"," ")+1)

I did not put "" around Name, it added that on it's own. It almost
worked. It put the word, Name in every entry in the Fname column,
instead of breaking out the word in front of the first space.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top