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
- Add Existing Fields
- Added the Fiscal Year Saving 2015 from the table.
- Right Click to view properties.
- Click on the "Event" tab and created an On Click: [Event Procedure]
- 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