Calculated Field Error

[WillM]

I have a several fields, calculated, that break apart a string of text at every open and closed parentheses. They work like they are supposed to with no errors. However, I want to use the result of this particular field in a combo box to populate another combo box with the other half of the string that will be split. The problem, I think, is that since the field is calculated, it will not allow me to select it in the combo box, saying that the field can't be edited.

So, I added the same field names to the table that the query was built on, however when I run the query, it says that there is a syntax error with a comma in the code. If I take out the table in the query, (not using the SQL side, just the query builder), it works again with no problem.

The text I am breaking apart looks like this:
1.234(a)(1)(A)(2)(b)(i)(-a-)

I need this field to break the text up at the (1) mark, so the code I am using looks like this:

PrinSubChap1: IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))

The other fields I have do the exact same thing, written the same way, with adjustments made to the position (+3,0).

I have stared at this for awhile and can't figure out why it is giving me an error and research on the web doesn't appear to cover my question.

This is the error message:
Syntax error (comma) in query expression '[mytable].[IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))]

Thanks for reading/looking!

 

[llkhoutx]

Make your calculation and then store the result in the combox box.

 

[WillM]

Thanks for the input! I actually decided to just nuke the 2nd combo box and store the values in a listbox and that worked.