Nested IIF Statement (and Select Case) with Date Ranges

[patjones]

Hi LB -

Well, it's been roundabout getting to this point, but I think your explanation about how the calculation works really clarifies matters nicely.

So, you have text boxes on your form that the user enters numbers into:

[U/L Prem]
[U/L Mod]
[Umb Mod]
[Effective Date]

And the idea is that three other text boxes, one for each level, will be filled in according to those four pieces of data after clicking a command button (which I have called "cmdCalculatePr em"):

[Umb Prem 1]
[Umb Prem 2]
[Umb Prem 3]

If I'm reading the stages of your calculation correctly, it looks like

[Umb Prem 1] = 0.09*[U/L Prem]*[U/L Mod]*[Umb Mod]

and similarly for the other two levels. In the On Click event for the command button, we could do something like this:

Private Sub cmdCalculatePrem_Click()
 
Dim dteEffectiveDate As Date
Dim rate, rate2, rate 3 As Variant
 
dteEffectiveDate = CDate(Me.[Effective Date])
 
'Get the rates for the three levels...

rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 1"))
 
rate2 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 2"))
 
rate3 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 3"))

'Now calculate the three respective premiums...

Me.[Umb Prem 1] = rate1*[U/L Prem]*[U/L Mod]*[Umb Mod]
Me.[Umb Prem 2] = rate2*[U/L Prem]*[U/L Mod]*[Umb Mod]
Me.[Umb Prem 3] = rate3*[U/L Prem]*[U/L Mod]*[Umb Mod]

End Sub

This will be dependent on having values in all of the input boxes; if you want to ensure that there are values in all the boxes before the user clicks the calculate button, there are many ways of doing that and we could discuss it after getting this basic part of it working.

Let me know what you think.

Pat

[patjones]

I just quickly made up a table and form to test this out and it seems to work OK. A few things to note...

I edited the code for the second Dim statement to read

Dim rate1, rate2, rate3 As Variant

In the table's design, don't set fldLevel to be a primary key - otherwise it won't let you use 1, 2 or 3 more than once in that column (which presumably you do want in order to account for each level in various years). In addition, the properties for fldRate should specify that multiple decimal places are allowed (I'm not sure how many you need).

Also, each level for a particular date range or year has to be present. If, for instance, level 2 is not present for year 2009 (or whatever the respective date range is), then the code will fail on the "rate2" calculation because [fldLevel] won't be present. If in fact each level will always be there, this won't be an issue.

Let me know how it works.

Pat

[LBinGA]

Once again, thank you, Pat. Sorry this is taking me so long to test out. They've had me working on another project mean time....sigh.

Ok, I added the code to cmdCalculatePre m's On Click event. I'm getting the error:
Run-time error: 3464: Data type mismatch in criteria expression.

There are a couple of questions I have regarding it, in my effort to understand what it is we're doing:
1. Why do you use [Effective Date] in the first line? I changed this to dteEffectiveDat e already so will this make it not know what to look for?

2. How am I differentiating the fldLevel's and fldRate's from one another. I put the field in the form 3 times and of course, when I change one, the others change....? Having a hard time wrapping my mind around that one.

Also, there may be times, in fact, most times that the user chooses to enter only in Level 1 OR Level 2 OR Level 3, etc.....they may only have one level to rate.

Very few times will all fields be filled.

Hope to get back to you rather quickly on this, as I've put everything else aside for today & tomorrow to work on it.

The good news is that it IS working with the first suggestions given at the top of this thread, and nicely....but I know this is a more succinct way so if you continue to have patience with me, I'm still here to learn. :D

thanks so much again,

LB

[patjones]

LB -

In regard to your error, can you tell me what line the code is breaking on? You can do this by setting a breakpoint somewhere close to the top of the procedure, then stepping through the code using the F8 button until the error comes up.

For the first issue, the reason I'm assigning dteEffectiveDat e to the text box value, and then using it in the lookup is to ensure that it is in fact a date. Sometimes people try to use dates taken from text boxes...but they aren't really dates. For example, #12/21/09# < #03/18/10# evaluates to TRUE, but "12/21/09" < "03/18/10" evaluates to FALSE. So my using dteEffectiveDat e is really just my way of making sure that you're using a date type and not a string type.

For the second issue...I think the problem that we're having here is partially a matter of semantics, and partially me not fully understanding how your data is structured. My understanding of your situation is that you simply have three input values:

[U/L Prem]
[U/L Mod]
[Umb Mod]

which then become the input for the calculations of

[Umb Prem 1]
[Umb Prem 2]
[Umb Prem 3]

where the only difference between Umb Prem 1, 2 or 3 is whether or not you use rate1, rate2, or rate3 for levels 1, 2 or 3 respectively.

I know it's hard to discuss these matters writing back and forth like this. We'll get it worked out one way or the other!

Pat

[LBinGA]

Ok, Pat, I think I see. I did F8.
The first line it breaks on is:
rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDat e & "# AND [fldDateUpper] >= #" & dteEffectiveDat e & "# AND [fldLevel] = 1"))

I can't get it to move past that.

Also, my question on the CDate line
dteEffectiveDat e = CDate(Me.[Effective Date])
was why use [Effective Date] here and not [dteEffectiveDat e]? I renamed the field to [dteEffectiveDat e] so I'm not sure what it's looking at....

In the formula we put:
Me.[Umb Prem 1] = rate1 * [U/L Prem] * [U/L Mod] * [Umb Mod]
Me.[Umb Prem 2] = rate2 * [U/L Prem] * [U/L Mod] * [Umb Mod]
Me.[Umb Prem 3] = rate3 * [U/L Prem] * [U/L Mod] * [Umb Mod]

It's actually:
U/L Prem1 * U/L Mod1=U/L Man1*rate1=Man XS1*Umb Mod1=Umb Prem 1
U/L Prem2 * U/L Mod2=U/L Man2*rate2=Man XS2*Umb Mod2=Umb Prem 2
U/L Prem3 * U/L Mod3=U/L Man3*rate3=Man XS3*Umb Mod3=Umb Prem 3

NOTES:
Bolded fields are manually entered.
Only one layer may be chosen and it could be layer 2, for example. Rarely will they rate all three layers but it could happen.

So, it's set up now that they User enters whatever fldLevel they wish to use, enter in the U/L Prem & U/L Mod, if any, hit calculate?

There is then another calculation on the other side of the fldRate then, that needs to calculate the Umb Prem (1, 2 or 3), based on if there is an Umb Mod (1, 2 or 3) that gets entered.

Or shall it all be done with one Calc button?

PS:
The rate table now looks like this, hope it comes across:

ID fldDateLower fldDateUpper fldLevel fldRate
1 1/1/10 12/31/10 3 .09
2 1/1/10 12/31/10 2 .13
3 1/1/10 12/31/10 3 .22
4 1/1/11 12/31/11 1 .11
5 1/1/11 12/31/11 2 .19
6 1/1/11 12/31/11 3 .33

thanks once again, pat....

LB

[patjones]

Ok, Pat, I think I see. I did F8.
The first line it breaks on is:
rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDat e & "# AND [fldDateUpper] >= #" & dteEffectiveDat e & "# AND [fldLevel] = 1"))

This indicates that there is a mismatch in type somewhere in the criteria argument. On this line, try replacing dteEffectiveDat e with the value that is in the text box directly: Me.[Effective Date].Value.

This would basically remove the need to calculate dteEffectiveDat e = CDate(Me.[Effective Date]), which is what I think your other concern was.

It's actually:
U/L Prem1 * U/L Mod1=U/L Man1*rate1=Man XS1*Umb Mod1=Umb Prem 1
U/L Prem2 * U/L Mod2=U/L Man2*rate2=Man XS2*Umb Mod2=Umb Prem 2
U/L Prem3 * U/L Mod3=U/L Man3*rate3=Man XS3*Umb Mod3=Umb Prem 3

I don't understand what you're trying to say here. If you tell me that you have inputs A, B, and C, and need to calculate D = A*B*C, or D = (A/B) * C, or D = (A+C) / B, or whatever, I can understand that.

What you've written is A*B = C*D = E*F = G. In an earlier post, I was trying to simplify this into something we can use. You have to tell me what the formula is in terms of one "=" that we can then implement in code.

Pat

[LBinGA]

Hi Pat:
Ok, I'm sorry I'm not explaining it properly. The formula would be exactly how you've written it:

[B]A[/B]*[B]B[/B]=C*D=E*[B]F[/B]=G

     A    *     B    =     C   *  D   =    E   *    F    =     G
U/L Prem1 * U/L Mod1 = U/L Man1*rate1 = Man XS1*Umb Mod1 = Umb Prem 1
U/L Prem2 * U/L Mod2 = U/L Man2*rate2 = Man XS2*Umb Mod2 = Umb Prem 2
U/L Prem3 * U/L Mod3 = U/L Man3*rate3 = Man XS3*Umb Mod3 = Umb Prem 3

I haven't called the fldLevel.

So on my Form, when I had calculated fields running the whole shooting match, the User would enter:
U/L Prem or A
either tab through or change the default of 1.0 on U/L Mod or B
and the calculation appeared in U/L Man or C

The user then just tabbed over to
Umb Mod or F
and Tabbed out

Man Excess or E
and
UmbPrem or G
were both calculated automatically within the fields.

I then moved it to the VBA below, and except for a pesky Update or Cancel Update error (for which I posted another question a bit ago), it works right now:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'*******************Prem Ops Mod**********************************
    If Me.Prem_Ops_U_L_Mod_1 = 0 Then
        [Prem/Ops U/L Manual 1] = [Prem/Ops U/L Prem 1]
    Else
        [Prem/Ops U/L Manual 1] = ([Prem/Ops U/L Prem 1] / [Prem/Ops U/L Mod 1])
    End If
    
    If Me.Prem_Ops_U_L_Mod_2 = 0 Then
        [Prem/Ops U/L Manual 2] = [Prem/Ops U/L Prem 2]
    Else
        [Prem/Ops U/L Manual 2] = ([Prem/Ops U/L Prem 2] / [Prem/Ops U/L Mod 2])
    End If
    
    If Me.Prem_Ops_U_L_Mod_3 = 0 Then
        [Prem/Ops U/L Manual 3] = [Prem/Ops U/L Prem 3]
    Else
        [Prem/Ops U/L Manual 3] = ([Prem/Ops U/L Prem 3] / [Prem/Ops U/L Mod 3])
    End If
    '*******************Prem Ops Mod 1 **********************************
Dim Effective_Date As Date
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
    [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
    [Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]

ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
    [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.10)
    [Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]

ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
    [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.11)
    [Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]

End If

    
'*******************Prem Ops Mod 2**********************************

If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
    [Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.13)
    [Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]

ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
    [Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.14)
    [Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]

ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
     [Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.15)
    [Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]

End If

'*******************Prem Ops Mod 3 **********************************

If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
    [Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.2)
    [Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]

ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
    [Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.21)
    [Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]

ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
    [Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.22)
    [Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]

End If

'*************************************Prods*************************

If [Prods U/L Mod A] = 0 Then
    [Prods U/L Manual A] = [Prods U/L Prem A]
Else
    [Prods U/L Manual A] = ([Prods U/L Prem A] / [Prods U/L Mod A])
    
End If

If [Prods U/L Mod B] = 0 Then
    [Prods U/L Manual B] = [Prods U/L Prem B]
Else
    [Prods U/L Manual B] = ([Prods U/L Prem B] / [Prods U/L Mod B])
    
End If

If [Prods U/L Mod C] = 0 Then
    [Prods U/L Manual C] = [Prods U/L Prem C]
Else
    [Prods U/L Manual C] = ([Prods U/L Prem C] / [Prods U/L Mod C])
    
End If


'*******************Prods A*****************************************

If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prods Manual XS A] = ([Prods U/L Manual A] * 0.1)
[Prods Prem A] = [Prods Manual XS A] * [Prods Mod A]
End If

'*******************Prods B*****************************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prods Manual XS B] = ([Prods U/L Manual B] * 0.15)
[Prods Prem B] = [Prods Manual XS B] * [Prods Mod B]
End If
'*******************Prods C*****************************************
If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[Prods Manual XS C] = ([Prods U/L Manual C] * 0.22)
[Prods Prem C] = [Prods Manual XS C] * [Prods Mod C]
End If

'*************************************Auto*************************

If [AL U/L Mod] = 0 Then
    [AL Manual] = [AL U/L Prem]
Else
    [AL U/L Man] = ([AL U/L Prem] / [AL U/L Mod])
    
End If

'*******************Prods A*****************************************

If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
[AL Man XS] = ([AL U/L Man] * 0.1)
[AL Prem] = [AL Man XS] * [AL Mod]
End If
End Sub

God, I hope that makes sense to you....my head is swimming!

Thank you,
LB

[patjones]

LB -

I'm sorry that I just can't digest what you're trying to do. What I'm trying to say is that writing it like A*B = C*D = E*F = G is what's confusing me here. I need you to write out the formula in a simple A = B format, with no intermediate calculations. You have three input values...show me how they combine in one clean arithmetic statement that I'm able to code.

Pat

[patjones]

Looking at the long piece of code that you posted, there's no question that while it may work right now, it will create headaches for you down the road - both in terms of readability and making modifications. I'm trying to follow the logic of it but having a hard time.

I suggest continuing along the lines of using DLookup to pick out the rates, as we were doing before, which will eliminate the need to hard-code dates. It will also help, I think, to simplify your notation and make a clear distinction between what the VBA variables are, and what the text box names from the form are.

It also looks like you have a lot of intermediate calculations. For instance, it seems like

[Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
[Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]

could just be written

[Prem/Ops Prem 1] = 0.09 * [Prem/Ops U/L Manual 1] * [Prem/Ops Mod 1]

unless there is some other need you have for [Prem/Ops Manual XS 1].

So this is the general direction I would take...

Pat

[LBinGA]

Ok, I asked the underwriter to help me out with getting the calculation across and here is what we've come up with:

In letters:
G=((A/B)xD) x F

In field names:
UmbPrem=((UL Prem / UL Mod) x Rate) x Umb Mod

God, I hope that helps, Pat..... ?

[patjones]

OK, so how about this (it will calculate all three rate levels for layer 1)...

Private Sub cmdCalculatePrem_Click()
 
Dim rate, rate2, rate 3 As Variant
 
'Get the rates for the three levels...
 
rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & Me.[Effective Date] & "# AND [fldDateUpper] >= #" & Me.[Effective Date]& "# AND [fldLevel] = 1"))
rate2 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & Me.[Effective Date] & "# AND [fldDateUpper] >= #" & Me.[Effective Date] & "# AND [fldLevel] = 2"))
rate3 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & Me.[Effective Date] & "# AND [fldDateUpper] >= #" & Me.[Effective Date] & "# AND [fldLevel] = 3"))
 
'Now calculate the three rate levels for layer 1...
 
Me.[Umb Prem 1_1] = ( Me.[U/L Prem 1] / Me.[U/L Mod 1] ) * rate 1 * Me.[Umb Mod 1]
Me.[Umb Prem 1_2] = ( Me.[U/L Prem 1] / Me.[U/L Mod 1] ) * rate 2 * Me.[Umb Mod 1]
Me.[Umb Prem 1_3] = ( Me.[U/L Prem 1] / Me.[U/L Mod 1] ) * rate 3 * Me.[Umb Mod 1]
 
End Sub

Like you said previously, the user may only want one layer, or two, or all three. Try out the code for layer 1, and if it comes out the way you want, we'll figure out a logic construct to incorporate the other two layers...

Pat