Nested IIF Statement (and Select Case) with Date Ranges

[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

[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]

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

[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

[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]

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]

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]

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]

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]

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

[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

[LBinGA]

Hiya: Sorry for the late response. Been snowing like crazy in GA and everyone loses their heads!

I've tried the above and to be honest, it's a bit over my head, but I'm trying.

The fields: [U/L Mod 1] and [Umb Mod 1] are User entered fields that will be used to do further calculations to obtain a final [Prem/Ops Umbrella Prem 1] at the end of the calculation string. Keeping in mind that the "1" designation only refers to the one level, and that there are two other calculations for the other two levels 2 & 3, designated accordingly. (I also have A, B & C that calculate identically too, but I figured I best get 1,2 & 3 working first).

So, I begin with the User entered Fields:

[Prem/Ops U/L Prem 1] * [Prem/Ops U/L Mod 1]

the User enters the Mod (Default is 1.0), if any, or Tabs through

Those two fields multiply to calculate [Prem/Ops U/L Manual 1],

then comes the Umb Factor (or fldRate now), which is now subject to change based on the dteEffectiveDat e field,

Then, the [Prem/Ops Manual XS 1] is calculated by multiplying the fldRate times the [Prem/Ops U/L Manual 1]

Then, the User entered field of [Prem/Ops Umb Mod 1] * [Prem/Ops Manual XS 1] to end in the calculated premium [Prem/Ops Umbrella Prem 1] on the form.

This what it looks like on the form. User Entered fields are bolded:
1 [U/L Prem 1] * [U/L Mod 1]=[U/L Man 1] *.09=[Man XS 1]*[Umb Mod 1]=[Umb Prem 1]

the other 3 fields are calculated. I was fine as long as the fldRate never changed, however, the bosses just informed me that the fldRate is subject to change, sometimes as much as twice per year. Ugh.

Once again, thank you for your help.

SO, as of now, I've added the fldRate field to the form as well as the fldLevel and a button with the code above. I enter a U/L prem, click the Calculate Prem button & I'm getting an error on this line:

dteEffectiveDat e = CDate(Me.txtDat eEffective)

Compile Error: Method or Member not Found

I changed it to read dteEffectiveDat e = CDate([dteEffectiveDat e)

and it gives an error on this line:
Me.txtPrem1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDat e & "# AND [fldDateUpper] >= #" & dteEffectiveDat e & "# AND [fldLevel] = 1))

Complie Error:
Syntax Error

Of course, I changed the Me.txtPrem1 to read [Prem/Ops U/L Prem 1], and added the = sign.

I hope this all makes sense. If not, lmk and I'll try to clarify. I'll be happy to share my little db if you think that would help answer the questions.

LB in GA

[patjones]

I want to emphasize that what I'm doing is just picking out what your rate should be. When you write out

1 [U/L Prem 1] * [U/L Mod 1]=[U/L Man 1] *.09=[Man XS 1]*[Umb Mod 1]=[Umb Prem 1]

I'm not sure what, for instance [U/L Mod 1] or [Mas XS 1] are, where those values are coming from, or what exactly it is you are trying to show on the form as the final result of the calculation. What I wrote out for you earlier this afternoon will simply display the rate (.09, .11, etc.) in a text box for the appropriate level on the form. But it seems like you might be trying to display the actual premium?

I think we're on the right track here, but I just want to make sure I've pinpointed exactly what end result you're seeking and how to easily display it on the user interface.

Pat

[patjones]

Well, I think for sake of simplicity, maybe you could just automatically display all three levels upon entry of the effective date. For instance, you can have txtDateEffectiv e, which is what the user types their date into, then three text boxes: txtPrem1, txtPrem2, txtPrem3. And a button: cmdCalculatePre m.

In the On Click event of the button, do something like:

Private Sub cmdCalculatePrem_Click()

Dim dteEffectiveDate As Date

If IsNull(txtDateEffective) Or txtDateEffective = "" Then

     Msgbox "Please enter an effective date!",  vbOKOnly + vbExclamation, "Date Entry"
     Exit Sub

Else

     dteEffectiveDate = CDate(Me.txtDateEffective)

     Me.txtPrem1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "# AND [fldLevel] = 1))

     Me.txtPrem2 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "# AND [fldLevel] = 2))

     Me.txtPrem3 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "# AND [fldLevel] = 3))

End If

End Sub

Doing it this way relieves you of having to setup check boxes and test in the code for which ones the user checked off...

Pat

[LBinGA]

Hmmm...now that's a very good question. Before, the User only need enter information in the first field ([U/L Prem]) of the level, each indicated meerly by a Label named 1, 2 or 3.

Those fields for each level are actually indicated as:
1 [U/L Prem 1] * [U/L Mod 1]=[U/L Man 1] *.09=[Man XS 1]*[Umb Mod 1]=[Umb Prem 1]

and 2 and 3 the same way. Using the If/Then statement, I repeated it 3 times, one time each for each level.

:/

I know that's really amateur-ish, but it's the only way I could figure out to do it. Do you have a suggestion for me? Would a combo or check box be the better?

Thank you, once again,

LB in GA