splitting data in one field to two fields

[JH40]

I'm trying to take a name field from a table and split it into two fields -- first and last name -- with a query.

Current column: "Doe,John"

Need to split in to two columns:
Last:"Doe"
First:"John"

Can someone help me with the expression on how to achieve this?

Thanks so much!

 

[SimonB1978]

Hi,

This should work:

SELECT Left(name_field, (Iif(InStr(1, name_field, ',') > 0, InStr(1, name_field, ',')-1, 0))) as last_name, Mid(name_field, InStr(1,name_field, ',') + 1) as first_name from table_name

BTW, the Iif is there to take care of the case where there would be no comma in the name.

HTH,

Simon B.