Hi ..

I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.

My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?

I need to ammend the commission based on a figure I input in a text box from the form view.

Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00

PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks

Query
Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))

First of all, I have sorted the code out, by line, and by IIF section so it is readable...Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",

IIf([Blotter]![Quantity]*[Clients]!<[Clients]![USD_1],[Clients]![USD_1],
[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or
[Shares]![Currency]="EUR",

IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],
[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),
[u][IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],
[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))What if I need to charge a special commission for that specific trade ?

I need to ammend the commission based on a figure I input in a text box from the form view.

Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00I can't believe you are asking this question! After writing the complicated nest from your post! Maybe you are just all "nested out". The bottom here, if I am correct, is that you need to replace the value obtained from the query with a "special commission" value, if one applies. And, if one does apply, it will be listed in the form's text box. If the SQL you already have written works just fine for returning your commission value, then all you need is another (outside) IIF statement to add to the mix. Like this...Commission: IIF(Forms!YourForm!TextBox Is Null,

IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",

IIf([Blotter]![Quantity]*[Clients]!<[Clients]![USD_1],[Clients]![USD_1],
[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or
[Shares]![Currency]="EUR",

IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],
[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),
[u]IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],
[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2]))),

Forms!YourForm!TextBox)

Hi Adam,

Thanks for your reply. It was really useful. I guess your right, I reached the point where my IIf looks like chinese :P