Calculated field - variable calculation dependant upon another field value

Question

Hi all, 

I am trying to create a calculated field which has two end results, based on the value of an option field.  For example if the option field in my form is set to X then i need my calculated field to be set to 10 weeks from the matter opened date (which is another field in my form).  If my option field is set to Y then i need my calculated field to be set to 15 weeks from the matter opened date. 

Can anyone point me in the right direction as to how to achieve this please.  I have managed to achieve the initial calculation but not sure how I go about adding in the additional option. 

Many thanks

Answer 1

So the fields are in two different tables? If so I doubt you can use a calculated field like that. You might be able to add some Code in a form.

Answer 2

Thanks for your reply, and your help...

On applying the changes, I have realised that the "option" field as I have referred to is actually held within the main record, and the calculation needs to be within the sub-field.  I therefore tried applying the following but just received a result of "=Name?".  Do I need to alter this to account for the main record?

=[MatterOpened]+IIf([Main]![Optionfield]="Y",112,182)

Answer 3

Thanks for your reply, and your help...

On applying the changes, I have realised that the "option" field as I have referred to is actually held within the main record, and the calculation needs to be within the sub-field.  I therefore tried applying the following but just received a result of "=Name?" 

Have I referenced the "main" field incorrectly?

=DateAdd("ww",IIf([Main]![Optionfield]="Y",112,182),[MatterOpened])

Answer 4

First, a Calculated field is done on the table level its a special type of Datatype. It sounds like you are trying to use expressions in controls on a form. Which brings up a second point. You don't have fields on a form, you have CONTROLS that may or may not be bound to fields in a table. This s a subtle but Important distinction. And the Third point is you refer to option fields. its not clear what that is. An Option Group is a control that allows you to select one of several options. It sounds like you may be using radio buttons to select/deselect values. If so radio buttons are either True or False, they cannot be equal to x or Y. Also an Option Group returns a numerical value, not a letter value.

All that being said. If you want to place an expression as the control source of control on a form, you would use something like this:

=DateAdd("ww",IIF(optionfield="X",10,IIF(optionfield="Y",15,0)),[MatterOPened])

Now you will need to adjust that for the exact controlnames and values that match your form. But that should do it for you.

Let me know if you need more help with this.

Answer 5

You can set the Expression to the following substituting your actual field names:

[MatterOpenedDate]+IIf([OptionField]="X",70,105)