Using an IF statement in the control source

[stacy0966]

Hi,

I have a form with four fields. They are as follows:

1. Sales – This is a manual input.
2. Expense – This is manual input.
3. Profit = Calculated with formula (sales – expense).
4. ProfitPerc – Calculated with formula (profit / sales).

Everything works fine. My problem is that I do not want the formula for field 4 to calculate if sales are equal to zero. I thought I would be able to enter an IF statement in the control source (ie. If(sales <>0, profit / sales), 0) but it does not work. I was able to get it to work using an if then statement on the “On Exit” property of the sales field. For this to always work I have to also enter it on the expense “On Exit” property . I was trying to find another way. I would prefer to use the control source. Any ideas would be greatly appreciated.

Regards,

Stacy

 

[KenReay]

Hi

=iif([sales] <>0, [profit] / [sales]), 0)

should work

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

=iif([sales] <>0, ([profit] / [sales]), 0)

should work

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[stacy0966]

Thanks Ken! This worked. Can you also use nested IF statments in the control source?

Thanks,

Stacy

 

[randy700]

You can "nest" the iif statements like this...

ShippingCharges = iif(price > 100, 5, iif(price > 50, 3, 1))


Randy

 

[KenReay]

Hi

It is IIF not IF

Yes you can have:

=IIF(Condition,IIF(condition,true,false),FAlse)

if that is what you mean

however personally, I avoid using such things if they become too complex, they lead to too much heartache when debugging or maintaining the application, in that case I would tend to write a user function which I can test "offline" and use in the SQL, or in the control source as necessary

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK