=[Commitments]/((iif(IsNull([S&S#Commitments]) OR [S&S#Commitments] = 0 ,0, [S&S#Commitments]+Nz([TotalCommitments],0))
Hope I got the parens right. Ditto on the names. Really poor practice. You are working with a relational database NOT a spreadsheet. The user doesn't see the internal names so you should cater to VBA when naming objects and leave the special characters and embedded spaces for labels on forms and reports.
I knew that but forgot to move the Iff() thanks for the reminder.
= iif(IsNull([S&S#Commitments]) OR [S&S#Commitments] = 0 ,0, [Commitments] / ([S&S#Commitments]+Nz([TotalCommitments],0))
This assumes you want to return 0 if [S&S#Commitments] is null or zero. If you want to return [Commitments] or do a different calculation, you need to expand the expression. We can't read minds.
Had the original backwards. I've attempted this: =[Commitments]/((iif([FY S&S].[Report]![S&S#Commitments] isnull,[TotalCommitments],[FY S&S].[Report]![S&S#Commitments]))+[TotalCommitments])
When [FY S&S].[Report]![S&S#Commitments] is zero, I get the #Type! error. When it is not zero, this report works properly.
Appreciate the feedback on naming. I am self taught and just making it work. What is meant by the comment to 'cater to VBA'?
There are many do's and don'ts when it comes to naming things in access/vba.
Don't use any spaces, special characters, reserved names, etc.
Users do not see much of what's behind the scenes like table names, field names, variables, etc. so its best to cater to what vba expects to see rather than what the user sees. For instance "Name" Name is a reserved word so to use it you would prefix it like txtName. Your user doesn't see this but the label for the textbox can have the caption "Name" which the user does see. Hope this makes sense.
That is why you move the test for 0/null OUTSIDE of the calculation. Then you only do the calculation if you are NOT going to divide by zero. Look at my example in #9 again.
IIf (is the field null or zero, true value, calculation)