Formula to Text Conv

[firestorm998]

I currently have the following formula as the control source for an Entitlement field:
=Round([Position1]/[Stk Ratio 2]*[Stk Ratio 1],2). Occasionally Stk Ratio 2 or Stk Ratio 1 is unknown and users enter 'TBC' but obviously this returns an 'Error!' where the formula sits as its not numeric.

How could I adapt this to pick up if Stk Ratio 1 or Stk Ratio 2 contains 'TBC', overide the formula to convert to text or just somehow show 'TBC' in the Entitlement field?


Thanks in advance.

 

[boblarson]

This SHOULD do it for you:
=IIf([Stk Ratio 2] = "TBC" OR [Stk Ratio 1] = "TBC","TBC",Round([Position1]/[Stk Ratio 2]*[Stk Ratio 1],2))

 

[Scottyk537]

If the Entitlement field is not linked to a table and is just used on a form for the user:
If "TextFormula" control source is
=Round([Position1]/[Stk Ratio 2]*[Stk Ratio 1],2)

Create another text box "TextShow" with it's control source as
=iif(iserror([TextFormula]),"TBC",[TextFormula])

Now hide "TextFormula" add use "TextShow" to display

 

[boblarson]

Mine should work if the control source is the formula as it isn't bound to a field of any specific type.

 

[firestorm998]

Works like a charm, many thanks guys