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

Splitting one field in a table into two

P: 37
Good afternoon Gurus,

I have a table in Access that has a name field listed as follows:


All in the same field, and I want to split the first name from the last into two individual columns, how would I go about this without using a splitter software?
Thank you in advance.
Oct 15 '07 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,072
You can use a query for this, but the result's will only deal with 2 words in the field. Names like "John Paul II Rocky Sr" can't be handles this way.

Expand|Select|Wrap|Line Numbers
  1. select left([YourFieldname],instr([YourFieldname]," ")-1) as FirstWord, mid([YourFieldname],instr([YourFieldname]," ")+1) as LastWord, ...
Having this working will allow you to change it into a maketable query to store the final results in a new table.

Oct 15 '07 #2

Expert 2.5K+
P: 3,532
There's a number of ways to do this. One would be to make a simple select query based on you table (if you don't already have one) and make these two calculated fields:

Expand|Select|Wrap|Line Numbers
  1. FirstName: Right(OriginalName, Len(OriginalName) - InStr(OriginalName, " "))
Expand|Select|Wrap|Line Numbers
  1. LastName: Left(OriginalName, InStr(OriginalName, ";") - 1)
where OriginalName is the name of the field you start out with.

Then use the query behind your tables, reports, etc, and refer to the fields LastName and FirstName as you would any other field. This is dependent, as is all parsing, on the original data being entered in the exact format you indicated.

Welcome to TheScripts!

Linq ;0)>
Oct 15 '07 #3

Expert 2.5K+
P: 3,532
And it's a tie at 4:29 between Nic;o) and ;0)>! But you missed the semicolon between the two names!

As my signature sez, There's always more than one way to skin a cat!
Oct 15 '07 #4

P: 37
Thank you for all your help.
Oct 15 '07 #5

Post your reply

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