Find string between two characters in a field

I am using the code

=IF(IsError(FIND(")",[dockey])),"",IF(IsError(FIND("(",[dockey])),"",Left(Right([dockey],Len([dockey])-FIND("(",[dockey])),FIND(")",Right([dockey],Len([dockey])-FIND("(",[dockey])))-1)))


and it is kicking out #Name?


[dockey] is the field which has the xxxxxxxxxxxxxxx(yyyyyy)zzzzzz string.

Where are you doing this VBA, Excel or Access?

It's an Access Database.  Using VBA in the modules....

Take your string and make
myArray = split(myString,"(")
'xxxxxxxxxxxxxxx(yyyyyy)zzzzzz
This will create an array where you have:
myArray(1) = "xxxxxxxxxxxxxxx("
myArray(2) = "yyyyyy)zzzzzz"

Then you only have to do
myParenthesisArray = split(myArray(2), ")")

And you'll have:
myParenthesisArray(1) = "yyyyyy"   'what you are looking for
myNameArray(2) = "zzzzzz"

test this


dim str as string, vStr as string
vStr="xxxxxxxxxxxxxxx(yyyyyy)zzzzzz"

str=mid(vstr,instr(vStr,"(") + 1, instr(vStr, ")") - 1 - instr(vStr, "("))

Capricorn1's method is the way that I would do this, but if you are going to use this as a control source, you would be better off using a function, so that you can handle the case where the field you are testing is NULL or doesn't actually contain "(" and ")" in that sequence.

r0bertdenir0 has the right idea, but his function doesn't take into account that, the search field could be null or blank, or that the character that he is using to identify the end of the search string could appear before the one that starts the process.

You need to decide what your business rules are if the string is not formatted the way you expect, and then write a function that will return what you want in those cases.

Yet another:

vStr="xxxxxxxxxxxxxxx(yyyyyy)zzzzzz"

? Array(Replace(Replace(vStr,"(","|"),")","|"))(1)
yyyyyy

Hi Everyone - thanks for your comments.  I took Robert's suggestion and had worked around it. It makes sense the other solutions but I had already made the changes to the code so I am rewarding him the points