The CC Email address in SharePoint comes over to Access look like
this: Person Name <PersonEmail (AT) mail (DOT) com>

How would you go about removing everything before and including the
";" and remove everything and including the "&"?

Thanks in advance.

Laura

musicloverlch wrote:
us what you wanted rather than trying to tell us about it. I think you want
to be left with this:

PersonEmail (AT) mail (DOT) com

so it's just a matter of using string functions (Left, Len, Instr, Right,
etc.) to do it. Taking it step-by-step:

1. Strip the last 4 characters:

SELECT Left([CC Email],Len([CCEmail])-4) As FirstStep ...

2. Encapsulate the query in step 1 in a subquery so you don't have to repeat
the calculations and strip the beginning characters by finding the position
of the ; and using that in a Mid function:

SELECT Mid(FirstStep,Instr(FirstStep,";") + 1) As GoodEmail
FROM (
SELECT Left([CC Email],Len([CC Email])-4) As FirstStep ...) As q

using this two-stage approach allows you to test each stage by itself when
debugging.