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.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.

GaryThanks 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?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.......
No, I'm embarassed. It was completely obvious once I saw your correction. Thanks very, very, very much!