Argument not Optional Error when creating a formula to run when clicking on a field On Click: [Event

[Mary87]

Please can you assist.

I have spent 2 days trying to debug....

Just a brief introduction to the problem.

I have created a form in MS Access 2010, the fields feed into a table "Cost Saving Tracker" form.
This forms calculates the amount of cost savings for the fiscal year ( from the start date to the end date (start date +720 days).

The cost saving amount depends on the field "One off or Recurring Cost Saving". The Fiscal Year starts on Oct and ends November. If it is a Recurring Cost Saving the Cost saving must be split accordingly.

For example if the start date is 01/09/2015 (Fiscal Month 11) and the end date being (01/09/2015 + 720 days=16/08/2018).
Cost Saving Amount = 150000
Monthly Cost Saving - 12500 (150000/12).

Then the Calculations should be as follow:
Fiscal Year 2015= 137500
Fiscal Year 2016= 150000
Fiscal Year 2017= 12500 (with the first and last year reconciling back to the Cost Saving Amount). The last year calculation being (Fiscal Month-12)*-1.

The way I have approached this is as follows, I created a form

1. Add Existing Fields
2. Added the Fiscal Year Saving 2015 from the table.
3. Right Click to view properties.
4. Click on the "Event" tab and created an On Click: [Event Procedure]
5. The formula being = IIf([One Off or Recurring Cost Saving] = "Recurring Cost Saving", IIf(Year([cboDate]) = 2015, [Monthly Cost Saving CAD] * IIf(Month([cboDate]) - 10 < 0, Month([cboDate]) + 2, IIf(Month([cboDate]) - 10 = 0,12, Month([cboDate]) - 10)),IIf(Year([cboDate] + 720) = 2015, ((IIf(Month([cboDate]) - 10 < 0, Month([cboDate]) + 2, IIf(Month([cboDate]) - 10 = 0, 12, Month([cboDate]) - 10)) - 12) * -1 * [Monthly Cost Saving CAD]),[Cost Saving Equivalent CAD]))) .

When trying to update the field. I would receive the following error "Compile error: Argument not optional".

However this formula works in Excel.



Thanks

[John_G]

I think The first IIf in the formula is missing a "Value if False", which would be in the very end of the formula somewhere. You will have to carefully deconstruct the statement to find out where you need it.

[June7]

Does not appear to be "Value if False" parameter for either:

IIf(Year([cboDate]) = 2015

IIf(Year([cboDate] + 720) = 2015

Suggest you build up the expression in stages. Start with:

IIf(a, b, c)

Build each a, b, c term independently then substitute.

[Mary87]

Thank you , it is working.