Removing parentheses and their contents from an access table field problem.

[mistermaoc181921]

Hi , i am very new to access less than 1 week since i started trying to build an horse racing database, i am trying to link data from 2 different sources via the horses name however one source displays this with the horses country of origin in parenthesis foe example FRANKEL(GB) and the other source displays the name as just FRANKEL, to be honest i haven't yet tried the link but guess it will fail.


I am therefore looking to get rid of the parentheses and their contents from an access table field and create another field without them.
In Excel i use the formula B1: =TRIM(REPLACE(A1,FIND("(",A1&"("),FIND(")",A1&")")-(FIND("(",A1&"(")-1),"")) and that works fine.
I guessed the solution for Access would be newname:TRIM(REPLACE([frhorse.NAME],FIND("(",[frhorse.NAME]&"("),FIND(")",[frhorse.NAME]&")")-(FIND("(",[frhorse.NAME]&"(")-1),"")) but this doesn't work as in returns undefined function "FIND" in expression error.
Is there any way anyone can help a very inexperienced user to understand a solution to this problem.
Thanks Mike

[lfpm062010]

Instead of getting rid of the ORIGINAL data. There is 2 things that you can do.

1) Create an extra column and update it with the correct name.
2) Create a query with the correct name and use that query to do the join.

[mistermaoc181921]

Thanks but its not the data i want to get rid of i have a field that returns example FRANKEL(GB) and in Query Design i want to create a field that returns just FRANKEL
There are thousands of names so cant do it manually.

[lfpm062010]

I was suggesting ways to solve your issue.

Is the name always before the first "("? If yes, try this

TRIM(MID([frhorse.NAME], 1, INSTR(1, [frhorse.NAME], "(") - 1))

[mistermaoc181921]

this is excellent and very close to want i need, however i forgot to mention some horse do not have anything in parenthesis and on these you solution returns #Func!
it works perfectly on the type of case i mentioned so thanks, how could i ammend the formula so it returns unchanged any name that do not have parethesis in original field, this is my fault for not mentioning this, sorry
so if original field contains FRANKEL i need it to return FRANKEL
if original field contains FRANKEL(GB) i need it to return FRANKEL

[lfpm062010]

IIF(INSTR(1, [frhorse.NAME], "(") > 0, TRIM(MID([frhorse.NAME], 1, INSTR(1, [frhorse.NAME], "(") - 1)), TRIM([frhorse.NAME]))

OR

Trim(IIf(InStr(1,[frhorse.NAME],"(")>0,Mid([frhorse.NAME],1,InStr(1,[frhorse.NAME],"(")-1),[frhorse.NAME]) )

[ItsMe]

Does the code offered in post #4 not work????

[mistermaoc181921]

Absolutely fantastic you have no idea how much frustration you lifted from me.
Many Thanks