iif (isnumeric(Schemno) and SchemeEnd = "Yes/No",<true assignment>,<false assignment>)
its possible that you cant use an IIF as a controls rowsource as design time, however you cna overcome this limitation by placing your code in a suitable event (such as firs on current event, before update, and relevant controls on change & lost focue events
The text box TxtDisplaySchemeNo shows the numerical value on the form ONLY after some actions otherwise it is null by default. Thats why I put "IF([TxtDisplaySchemeNo]>0".
and on the basis of this I need to have sum of InvAmt field from table where the criteria would be "SchemeNo= TxtDisplaySchemeNo and SchemeEnd = False"
These both fields (SchemeNo [Numeric type] and SchemeEnd [Yes/No type]) are from the same table called T_AdvPaySalesInvFooter.
I can get the result keeping 2 text boxes containg 2 diff. IIF statement and then garher into one but it would be lengthy and surely will reduce the fuction's speed.
I need to have both condtion in one IIF statement. So if the TxtDisplaySchemeNo would be greater than 0 it would check in table and get the sum.