Splitting one field in a table into two

[matrekz42]

Good afternoon Gurus,

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

LASTNAME; FIRSTNAME

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.

[nico5038]

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.

Try:

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.

Nic;o)

[missinglinq]

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:

FirstName: Right(OriginalName, Len(OriginalName) - InStr(OriginalName, " "))
 

and

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)>

[missinglinq]

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!

[matrekz42]

Thank you for all your help.