split function or whatever works in Access

[eli schre]

What is the code or expression used to return text separated by a comma. In other words, in a text field containing a last name separated from the first name by a comma, what do I do to return either the last name up to the comma or the first name after the comma. I can't use "left" or "right" because the names are all of different lengths. I've tried working with the split function but am not doing it correctly. Thanks for any help offered.

[gussery]

You can use a combination of left, right and instr (In String).  Instr returns a number equal to the first place it finds a specific character.

So Left([fieldname],Instr(",")) would return the leftmost characters up to and including the comma.  You can do Left([fieldname],Instr(",")-1 ) if you don't want the comma.

To get the right side you do Right([fieldname],Len([fieldname) -  Instr(",")).  Which takes the rightmost characters determined by taking the length of the string (len) and subtracting the position of the first comma from the left.

Gary

[catz]

Thanks very much Gary. I've gotten the Left function working but I'm getting an "Invalid Procedure Call" when I try the Right function. My syntax must be off but I'm not seeing it. This is what I typed in:
Right([PrimaryCaregiverName],Len([PrimaryCaregiverName])-Instr(","))

Any suggestions?

[gussery]

Yep, my fault, sorry. :-[Right([PrimaryCaregiverName],Len([PrimaryCaregiverName])-Instr([PrimaryCaregiverName],","))

Forgot to tell Instr what string to look at.



Gary
(very embarrassed)
That will teach me to reply before morning coffee.......

[catz]

No, I'm embarassed. It was completely obvious once I saw your correction. Thanks very, very, very much!