0 Calculated Field if tick box selected

[louisa]

Hi everyone,

I have a formula which works out commission based on various other fields.
On occasions i wont want it to be worked out and will want it to be zero but on others i will require it to be worked out.

If i place a tick box next to the commission field saying paid yes/no, if i select no is there a way the calculated value changes to a 0?

Any help greatly appreciated.

 

[missinglinq]

You don't day where/how you're doing this calculation, but basically

=IIF(YourTickBox = -1, [your calculation goes here], 0)

Linq ;0)>

 

[louisa]

I have a field that has a formula attached that will work out commission based on other fields, the total is then displayed in that box.
On occasions that figure will not be due so i would like to have a box of tick box that i could select which will set the figure to 0.
As there is a calculation formula behind the field i cannot just simply delete the figure and type zero therefore i am trying to find away that an on click would change the figure to a zero.

 

[boblarson]

I have a field that has a formula attached that will work out commission based on other fields, the total is then displayed in that box.
On occasions that figure will not be due so i would like to have a box of tick box that i could select which will set the figure to 0.
As there is a calculation formula behind the field i cannot just simply delete the figure and type zero therefore i am trying to find away that an on click would change the figure to a zero.

missinglinq gave you what you need to use in your control's control source. What is the calculation that you CURRENTLY have? He or I can then tell you how to modify it to do what he showed.

 

[louisa]

Hi,

The calculation i have in my field is =Nz([GrossOperatingProfit],0)/36*5

Many thanks for your help

 

[louisa]

Hi everyone i worked it out in the end so thankyou for all your help. Maybe you could help with another query i have?
I have another calculated field, with a control source of =Nz([GrossOperatingProfit],0)/2
i am now hoping that if i have a yes/no tick box and i select yes that i will be able to manually change the figure in the calculated field. Is there a way i could do this?

 

[louisa]

Hi Everyone,

I made some changes to my db and now my when i select my tickbox the value is not changing to zero as it was before, i have tried the following in the control source =IIF(CommissionPayable1 = -1, [=Nz([GrossOperatingProfit],0/36*5,0) can anybody advise where i may be going wrong?

 

[DCrake]

If you have cut and pasted the control source then you have a syntax error

=IIF(CommissionPayable1 = -1, [=Nz([GrossOperatingProfit],0/36*5,0)

=IIF(CommissionPayable1 = -1, Nz([GrossOperatingProfit],0/36*5,0)

 

[louisa]

Hi thanks for coming back to me, i have tried what you have suggested but i receive the following msg "The expression you entered has a function containing the wrong number of arguements"

 

[DCrake]

Syntax error

=

IIF(CommissionPayable1 = -1, Nz([GrossOperatingProfit],0[B][COLOR="Red"])[/COLOR][/B]/36*5,0)

Be careful though about div by zero

0/36*5 may cause an error. Also you should be bracketing you calculations correctly

=(((GOP/36)*5)

 

[louisa]

Hi, i can still not get this to work and i am getting soooo frustrated.
I have a tick box called CommissionPayable1 and a field called CommissionDue which has a calculated control field. All i want to happen is if i tick CommissionPayable1 then the CommissionDue will change to a zero. I cannot for the life of me get it to work. :-(

 

[DCrake]

Couple of things here.

1. On the forms OnCurrent Event

Check the status of the commission payable control (True/False)

If Me.CommisionPayable = True then
   Me.CommisionDue = Your Calculation Here
Else
   Me.CommisionDue = 0
End If

2.
Use this same logic on the On Click event of the check box.

Don't forget you should not be saving this calculation in a field as is can be generated via a query

 

[louisa]

Thanks you so much Dave, it is finally working. I have one query though, if i select the checkbox it doesnt change to zero instantly, it will just stay with the figure in it and if i move out of the record into another and then go back it has changed, but not in front of your eyes so to speak.

 

[DCrake]

Within your form VB section create the following public Function

Public Function UpdateCommission()

If Me.CommisionPayable = True then
   Me.CommisionDue = Your Calculation Here
Else
   Me.CommisionDue = 0
End If

Me.CommissionDue.Requery

End Function

Then in the Forms OnCurrent Event change the earlier code to

Call UpdateCommission

Do the ame in the check box click event

 

[louisa]

Hi Dave,

Thanks for all your help so far, i apologise if this seems strange but i have no idea what you mean by creating a public function in the VB form. I have never done that before and dont no where to even start.

 

[DCrake]

Ok

Design your form and switch to VBA mode F11.

Scroll right up to the top of the section and paste in the function provided. Don't forget to make the changes to the function to reflect the correct calculations then place the Calls in the appropriate places. If you have any difficulties post a copy of your mdb to look at for you.

 

[louisa]

Hi Dave,

I have removed the code in OnCurrent event and changed to Call Update CommissionDue, but i keep getting a Compile Error, expected end of statement. I have tried to research online but to no avail. I was going to upload a copy of my mdb but unfortunately it is split.

 

[DCrake]

What compile error are you getting?

Also stick the relevant objects into a new mdb and post that. You and anonomise the data if it is sensative.

 

[louisa]

Well i took out the code in the OnCurrent event and typed Call.UpdateCommissionDue and it comes up compile error expected end of statement, is that all i should be placing in the Call function?

 

[gemma-the-husky]

louisa

didn't you ask all this in another way before?

all of this "exception" coding seems to me to point to a database design errors. If you have a calcluation based on underlying data, I would say you want this to be done within the forms underlying query, not in the control.

some calcuations are very difficult to do "inline" ie with a single iif statement, and are easy to do with a function.

But you really need to make sure your database is storing at an "atomic" level all the data elements that enable you to perform the cacluation. Then changing the appropriate data element will refresh the computation automatically.