Breaking Lines Into Pieces

B

BR

I have a existing database with data formatted with the following:
Lastname, FirstName Middle

There are 1000's of such rows in the existing DB, and the format is
consistent.
I would like to be able to Break the line apart into 3 seperate Field Valies

Lastname Firstname Middle

Does any one know how to accomplish this.?

Thanks
 
S

Steve Schapel

BR,

Add the new fields to your table. Then make an Update Query based on
this table. In design view of the query, in the Update To row of the
query design grid, put...
Left([FullNameField],InStr([FullNameField]",")-1)
.... in the LastName column,

Mid([FullNameField],InStr([FullNameField]",")+2,InStrRev([FullNameField],"
")-InStr([FullNameField]",")-2)
.... in the FirstName column, and
Mid([FullNameField],InStrRev([FullNameField]," ")+1)

I didn't test these expressions, but they look pretty right :) As far
as I can see, the only time it would fail is if the comma is not in the
original data, or if the person has a double middle name. Or if you are
using a version of Access earlier than Access 2000 SR1, in which case
InStrRev() function won't work.
 
B

BR

Access is giving me an error that Microsoft Access did not update 11503
fields due to a type conversion failure.
Steve Schapel said:
BR,

Add the new fields to your table. Then make an Update Query based on this
table. In design view of the query, in the Update To row of the query
design grid, put...
Left([FullNameField],InStr([FullNameField]",")-1)
... in the LastName column,

Mid([FullNameField],InStr([FullNameField]",")+2,InStrRev([FullNameField],"
")-InStr([FullNameField]",")-2)
... in the FirstName column, and
Mid([FullNameField],InStrRev([FullNameField]," ")+1)

I didn't test these expressions, but they look pretty right :) As far as
I can see, the only time it would fail is if the comma is not in the
original data, or if the person has a double middle name. Or if you are
using a version of Access earlier than Access 2000 SR1, in which case
InStrRev() function won't work.

--
Steve Schapel, Microsoft Access MVP
I have a existing database with data formatted with the following:
Lastname, FirstName Middle

There are 1000's of such rows in the existing DB, and the format is
consistent.
I would like to be able to Break the line apart into 3 seperate Field
Valies

Lastname Firstname Middle

Does any one know how to accomplish this.?

Thanks
Click to expand...
Click to expand...
 
S

Steve Schapel

BR,

How does 11503 compare with the total number of records in the table?

Try running the Update one field at a time, and see if the problem
relates to all of them, or only one.

Try putting the expressions into the Field row of a Select Query, and
have a look at what it produces in case this gives you a clue to what is
not right.

Post back with the SQL view of the query you are trying with, in case I
can spot the problem.
 
Top