Splitting info from 1 cell into 2 cells in ACCESS

[dwapachee]

Hi,

I have a table with just over 1000 records in it. In there I have a column that lists peoples names in this format:
LASTNAME, Firstname

I need to split this into 2 separate columns, one for the last name and one for the first name. The table that this information is in has around 25 columns of information and I do not want to corrupt/lose any of the data already there.

Please help!!
Thanks!!
Danielle

 

[Remou]

It is possible to split the field to two new fields, but the split is unlikely to be completely accurate. First, you will need to back-up your database, as such a split will make permanent changes, then add two columns to your table called, say, LastName and FirstName. You now need an udate query. The query will look something like this, in SQL view:

[tt]Update tblTable Set LastName=Mid(CurrentName,1,Instr(CurrentName,",")-1), FirstName=(CurrentName,Instr(CurrentName,",")+1)[/tt]

Where CurrentName is the name of the field with names in it and tblTable is the name of the table.

 

[dwapachee]

Hi Thanks for the info. Sorry but I am still a little stumped. I have not done an update query in quite a few years, and SQL has never been my strong point.

Can you break it down a little for me, for clarity, I have put in the SQL view and added my actual field names (easier for me to read). I am not sure where to put this, under criteria? Thanks for your patience....it is extremely appreciated!!!

MSTR List Set LastName=Mid(Transferee,1,Instr (Transferee,",")-1), FirstName=(Transferee,Instr(Transferee,",")+1)


THANKS!!!!!

 

[Remou]

Open a query and go to SQL View (View menu) and paste in this:

Update [MSTR List] Set LastName=Mid(Transferee,1,Instr (Transferee,",")-1), FirstName=Mid(Transferee,Instr(Transferee,",")+1)

Replacing any text that is in the window. Return to Design View and see if all looks well, if it does, you can run ([red]![/red]=run) the query to update.

 

[dwapachee]

Hi,

Thanks for that...I did what you said and it seemed to work, but when I ran it it asked me to "Enter Parameter Value" then it lists "Last Name" and below that is a text bos that I can enter in data, I left it blank and clicked ok and it said it could not update.

Did I do something wrong?

Thanks!
Danielle

 

[Remou]

Did you notice the bit in my first post about backing up and adding two new fields (columns) to your table?

 

[dwapachee]

Yes, I did do both first, I backed it up and I had created 2 new fields, one called Last Name and one called First Name. I copied and pasted your code in your post and that is the result I received.

Not sure what happened there.....

Danielle

 

[Remou]

If you included spaces in the fields as you show above, you need to adjust the sql to include these spaces and add square brackets, like so:
[First Name]

Or else remove the spaces. Spaces are not recommended in field names.