split a name field (1 Viewer)

[jeo]

I have a name field in this format: smith, john
Is there a way to split this into 2 separate fields in a query?
I looked up the split function, but it's saying that this function doesn't exist.
I also thought this might work, but it doesn't.
SELECT left(name,instr(name,",")-1) as LastName, right(name,instr(name,",")+2)from table1

Can someone tell me what I'm doing wrong or recommend a way to do this?
Thanks.

 

[Rabbie]

Try

SELECT left(table1.name,instr(name,",")-1) as LastName, right(table1.name,instr(name,",")+2) from table1

 

[jeo]

since i'm in a query, i figured out that i don't need the actual table name, just the field name:
LastName: Left([name],InStr([name],",")-1) - this worked great to get the last name.
FirstName: Right([name],InStr([name],",")+2) - this didn't work at all...
it's not giving me any data back for this field. any ideas why?

 

[Rabbie]

Try this

FirstName: Right([name],len([name])-InStr([name],","))

I have now tested this and it should do what you want