Nested IIF's on form...

[gmlwong]

Hello all!

Here's one for ya...

On a form, I've added several calculated controls. Originating from an Excel sheet, I would think the same or similiar formulas would work in Access but no luck...check it out and see what you think.

[txtMonElap] = working; a field that calculates the difference between the current date and an earlier date,

[TermMonths] = working; a field for each record from original data entry, no problems...,

[txtU4Calc] = a formula taken from a working Excel sheet but not working when translated into Access. Performs a small nested IIF formula and looks like this: "=IIf([TermMonths]=120,0.05,IIf([TermMonths]=84,0.1,IIf([TermMonths]=72,0.15,IIf([TermMonths]=60,0.2))))"

[txtLossRatio] = a formula taken from a working Excel sheet but not working when translated into Access. Performs a basic conditional IIF statement and looks like this: "=IIf([txtEarnRes]>0,([txtPD]/[txtTTLEarnRes]),0)"

[txtPD] = a summed field that adds the total# of PD claims; works fine. No problem...

[ResRefund] = data entry field for a value found in another screen. I tried to set the control source to equal this other source but checked and checked only to get errors...with test data from data entry, this field works fine. No problem...

[txtTTLRes] = a simple formula in the control source that calculates the sum of fields called, [Reserve] and [ResRefund]. Works fine with the field [ResRefund]. No problem...

[txtEarnRes] = a monster calc that drives many of the calc's above and is the root problem why these other calc's aren't working. Looks like this: "=IIF([txtMonElap]>=[TermMonths],[txtTTLRes],IIF([TermMonths]<=48,([txtTTLRes]/[TermMonths])*[txtMonElap],IIF([[txtMonElap]<=9,0,IIF(And[txtMonElap]>9,[txtMonElap]<37),([txtMonElap]-9)*(([txtU4Calc]*[txtTTLRes])/27),IIF([txtMonElap]>36,([txtU4Calc]*[txtTTLRes])+(([txtMonElap]-[txtU4Calc])*[txtTTLRes])/([TermMonths]-36)),"")))))"

My 1st question: Are the nested IIF's correct in being able to use an "And" condition in the 4th nested statement?

I think that's where it's getting tripped up... thanks in advance.

 

[jfgambit]

Gonna throw out my 2 cents...but you can ignore it if it's not what you are looking for...

2 words....

Select Case

Example:

Select Case Me.txtMonElap

Case is >= Me.TermMonths
Me.txtEarnRes = Me.txtTTLRes

Case is <=48
Me.txtEarnRes = Me.txtTTLRes/(Me.TermMonths*Me.txtMonElap)

End Select

Of course you will need to add a Case for each condition that you have below.


Good Luck

 

[gmlwong]

Monster Calc almost solved...

Re: the Monster [txtEarnRes] calc...I'm able to get the results I would expect through the following level of this sucker but it blows up when I try to add any additional nested IIF conditions...

It works through; "=IIf([txtMonElap]>=[TermMonths],[txtTTLRes],IIf([TermMonths]<=48,([txtTTLRes]/[TermMonths])*[txtMonElap],0))"

Would VBA help or this calculation simply not allowed?

 

[gmlwong]

Hey Pat!

Thanks...as soon as I entered the next nested IIF (the And condition...), the value returns #Error but the formula works through the point just prior to that.

I'm going to try one more thing and then give the VBA code a try.