Instr and Mid function ERROR 1

[BrockLanders]

Hi
On this site I have found a similar example of what I am trying to accomplish but cannot seem to get it to work properly. I have an Access 2000 table with a field called CARRIER that has similar text values. An example of one of the values in the field is: PILOT (014789526).
In my query, I am trying to create a calculated field, called MyNumber, that only returns the value inside the parenthesis. So for the above example, the field MyNumber would return 014789526.
I have the following code for my calculated field, but keep getting #Error in the MyNumber field for every record.

MyNumber: IIf(nz(InStr(1,[CARRIER],"PILOT&quot)>0,Mid([CARRIER],InStr(1,[CARRIER],"(&quot+1,InStr(InStr(1,[CARRIER],&quot&quot+1,[CARRIER]," &quot-(InStr(1,[CARRIER],&quot&quot+1)),"&quot

I believe the syntax is correct, but it just set up wrong. Any ideas?

 

[PaulBricker]

Give this expression a try and see if it works.

IIf(Nz(InStr(1, [CARRIER], "Pilot&quot) > 0, Mid([CARRIER], InStr(1, [CARRIER], "(&quot + 1, (InStr(1, CARRIER], &quot&quot - InStr(1, [CARRIER], "(&quot) - 1), " &quot

Paul

 

[BrockLanders]

Yep, that worked exactly. I see what you did now. Thanks much