Split table field

[DIRENE]

Help, I see a lot of split field threads but I'm not getting now to did it. I have a table with fields name, firstname and lastname.

This is the field I would like to split to two other field

name: davis, Irene

I want

firstname: Irene
lastName: Davis

Then I can do away with the name field.

Thank you for any help Irene

 

[scriverb]

This type of issues are always a hit and miss situation. If you rule for the field Name is constant and complete then there will always be a comma(,) between the lastname and the firstname. I am going to use that rule for this code. Any records that don't follow that rule will not parse correctly and you will have to deal with them by hand.

Code for a Select Query to check the intended output:

Select A.Name, Trim(Mid$(A.Name,1,Instr(1,A.Name,",")-1)) as LastName, Trim(Mid$(A.Name,Instr(1,A.Name,",")+1)) as FirstName 
FROM your_table_name as A 
ORDER BY A.Name;

Code for an action Update Query to parse the name into the two existing fields:

UPDATE your_table_name as A SET A.LastName = Trim(Mid$(A.Name,1,Instr(1,A.Name,",")-1)), A.FirstName = Trim(Mid$(A.Name,Instr(1,A.Name,",")+1));

Post back with any questions that you may have.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[DIRENE]

Thank you so much Bob. That work really great and it was so easy.

 

[scriverb]

Great!! I am glad to help you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]