473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Nested IIF Statement (and Select Case) with Date Ranges

14
Hello all:
I'm having trouble with a nested If Statement working properly. I'm working in MS Access 2002 (OS Windows XP, ver 2002). The non-working code is going on the Form (a Single) as follows:
Expand|Select|Wrap|Line Numbers
  1. Dim Effective_Date As Date
  2. If Me.Effective_Date >= 1/1/10 And Me.Effective_Date <= 12/31/10 Then
  3.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
  4.     [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]
  5.  
  6. ElseIf Me.Effective_Date >= 1/1/11 And Me.Effective_Date <= 12/31/11 Then
  7.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.22)
  8.     [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]
  9.  
  10. ElseIf Me.Effective_Date >= 1/1/12 And Me.Effective_Date <= 12/31/12 Then
  11.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.33)
  12.     [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]
  13.  
  14. End If
The code will fill in the last solution regardless of the date. I have, however, had it work if I use Me.Effective_Date="1/1/10"
Given one choice ("equal to" only) be it a date or text and not a date range, the code works perfectly. I have also tried putting the dates in quotations within the range. It still does not work.

Alternatively, since there may be more than 2 or 3 date ranges in the future, I tried a Select Case as follows. It doesn't work either. It selects the first case only no matter what the Effective Date is.
Expand|Select|Wrap|Line Numbers
  1. Dim Effective_Date As Date
  2. Select Case [Effective Date]
  3. Case Is >= 1 / 1 / 10, Is <= 12 / 31 / 10
  4.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
  5.     [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]
  6.  
  7. Case Is >= 1 / 1 / 11, Is <= 12 / 31 / 11
  8.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.22)
  9.     [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]
  10. End Select
Any help or tips would be appeciated, including whether an If/Then Statement or a Select Case solution would be more efficient. Thank you in advance.

LB in GA
Feb 25 '10 #1
25 7324
patjones
931 Expert 512MB
Hi LBinGA -

Just from a quick look, I would ask you to try enclosing the dates in "#" signs. For instance,
Expand|Select|Wrap|Line Numbers
  1. ...
  2. If Me.Effective_Date >= #1/1/10# And Me.Effective_Date <= #12/31/10# Then
  3. ...
might work. Let me know whether this works out for you.

Pat
Feb 25 '10 #2
LBinGA
14
OMG! I can't believe it was that simple! I have been working on this ALL DAY, trying quotation marks, 2-digit years, 4-digit years, and standing on my head in the corner for the last hour, none of which worked save to give me a bigger headache.

Didn't realize I would have to mimic a Query.

Thanks, Pat!

Now, would you recommend I continue to use the IIf Statement over the Select Case going forward? I will need to add date ranges, ad hoc, as rates change over the future years....

Thanks again,

LB in GA
Feb 25 '10 #3
TheSmileyCoder
2,322 Expert Mod 2GB
Presumably you could have your date ranges stored in a table along with the Rate, and then retreive the rate by query or dlookup.

While " and ' are used to denote string literals, # are used to denote date literals. 1/1/10 is basicly read as 1 divided by 1 divided by 10. You can force access to convert a string to a date by using cdate("1/1/2010"), and I think in some cases if access is given a string, it might try to guess the conversion itself. But the 1/1/10 is just a match expression and access would have no wish to convert that.
Feb 25 '10 #4
patjones
931 Expert 512MB
I'm glad it worked out for you...

I personally consider "Case...Select" to be a more readable way of doing it, and there may be a performance benefit to using a Case structure.

The following article talks about a speed test that was designed to examine the performance of If...Else versus Switch in C#. As a disclaimer, I have never used C#, but the results favor Switch; I'm not certain whether this applies to VB as well.

http://www.blackwasp.co.uk/SpeedTestIfElseSwitch.aspx

Pat
Feb 25 '10 #5
LBinGA
14
Thanks, Smiley. Don't know why I wouldn't have tried the # signs. Duh.

I would, I'm sure, prefer to store the rates & dates in a Table to call by DLookup, but I don't know how to do that. This was my "baby-step" effort at resolving the issue.

I have an Access Inside Out book, a few other reference books and the internet to search. Any suggestion on where to start or what to google to get an idea of how to do a DLookup/Table solution you described would be greatly appreciated. :D

LB in GA <---slaps forehead with palm
Feb 25 '10 #6
patjones
931 Expert 512MB
Hi -

First, I would put
Expand|Select|Wrap|Line Numbers
  1. [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]
outside the Case...Select structure, provided that calculation never depends on the date range.

For DLookup...I would set up a table, let's call it tblRates for sake of argument, with fields: fldDateLower, fldDateUpper, and fldRate. In the code, your premium would be assigned as

Expand|Select|Wrap|Line Numbers
  1. Dim dteEffectiveDate As Date
  2. Dim rate As Variant
  3.  
  4. rate = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "#"))
  5.  
  6. [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] *rate)
  7. ...
The third argument in DLookup is a concatenated string, because if you put dteEffectiveDate inside with everything else it won't recognize it as a variable that is coming from the VB code; there are other ways to write this criteria as well, such as with the BETWEEN keyword just like what you would do in a SQL WHERE clause.

Also, rate apparently needs to be declared as a Variant then converted to a decimal, according the Access help page; hence the CDec conversion.

So, you end up write out the calculation once instead of doing it in the Case...Select. This is nice because then all you need to do is update the table every year, or however often, and the code here doesn't need to be modified.

Pat
Feb 25 '10 #7
patjones
931 Expert 512MB
I should point out that in my previous post...the first part about putting that line of code outside the Case...Select structure obviously won't matter if you use the DLookup method and get rid of the Case...Select.

Also, the comparisons in the DLookup criteria should use <= and >= so as to catch 1/1 and 12/31 in the date ranges.

Pat
Feb 26 '10 #8
LBinGA
14
Thank you, Zep! I'm testing it out this morning and I'll let you know what comes of it. Hopefully I can wrap my mind around it with enough coffee.

The If/Then code works great now but as you might guess, I have numerous premiums to apply it too and if I could have just one Tbl per set of rates that need to change annually, that would be ideal.

:D

LB in GA
Feb 26 '10 #9
LBinGA
14
Zepp,
I've copied the code over you generously suggested (changed it to included the = sign), added it to BeforeUpdate on the Form, made a tblRates and inserted the dates and rates for the level 1 (Prem/Ops Man Prem 1).

I'm not sure I understand how the one Rate Table will address each level. Will I need a Table for each level.

The Prem/Ops Form is designed like this:

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

2 [U/L Prem] * [U/L Mod]=[U/L Man] *.13=[Man XS]*[Umb Mod]=[Umb Prem]

3 [U/L Prem] * [U/L Mod]=[U/L Man] *.20=[Man XS]*[Umb Mod]=[Umb Prem]

The user can select any one or all three levels to rate and the rates will change on each layer depending on the Effective Date. So, basically, there are two variables, the Effective Date and the Level.

Perhaps it's just that I don't understand how to set up the tblRates properly, perhaps I shouldn't have put the code in BeforeUpdate on the Form, perhaps I need more coffee??? At any rate, based on the above I'm getting a Runtime Error 94: Invalid use of Null.

Big thanks,
LB in GA
Feb 26 '10 #10
patjones
931 Expert 512MB
Hi LB -

OK, in your original post it seemed like the rate depended on the date range only. I understand now though. I would set the table up like so...

fldDateLower fldDateUpper fldLevel fldRate

1/1/2008 12/31/2008 1 .09
1/1/2008 12/31/2008 2 .13
1/1/2008 12/31/2008 3 .22
1/1/2009 12/31/2009 1 .11

and so on...

Then, the DLookup would be modified like this...

Expand|Select|Wrap|Line Numbers
  1. ...
  2. Dim intLevel As Byte
  3.  
  4. intLevel = ? ? ?
  5.  
  6. rate = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "# AND [fldLevel] = " & intLevel))
  7. ...

Notice that I put ? ? ? in the assignment for intLevel...what is it on the form that indicates what level(s) the user wants? Check boxes? Drop-down?

Pat
Feb 26 '10 #11
LBinGA
14
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
Feb 26 '10 #12
patjones
931 Expert 512MB
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 txtDateEffective, which is what the user types their date into, then three text boxes: txtPrem1, txtPrem2, txtPrem3. And a button: cmdCalculatePrem.

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCalculatePrem_Click()
  2.  
  3. Dim dteEffectiveDate As Date
  4.  
  5. If IsNull(txtDateEffective) Or txtDateEffective = "" Then
  6.  
  7.      Msgbox "Please enter an effective date!",  vbOKOnly + vbExclamation, "Date Entry"
  8.      Exit Sub
  9.  
  10. Else
  11.  
  12.      dteEffectiveDate = CDate(Me.txtDateEffective)
  13.  
  14.      Me.txtPrem1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "# AND [fldLevel] = 1))
  15.  
  16.      Me.txtPrem2 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "# AND [fldLevel] = 2))
  17.  
  18.      Me.txtPrem3 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "# AND [fldLevel] = 3))
  19.  
  20. End If
  21.  
  22. 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
Feb 26 '10 #13
patjones
931 Expert 512MB
I want to emphasize that what I'm doing is just picking out what your rate should be. When you write out

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 27 '10 #14
LBinGA
14
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 dteEffectiveDate 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:

dteEffectiveDate = CDate(Me.txtDateEffective)

Compile Error: Method or Member not Found

I changed it to read dteEffectiveDate = CDate([dteEffectiveDate)

and it gives an error on this line:
Me.txtPrem1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# 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
Mar 3 '10 #15
patjones
931 Expert 512MB
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 "cmdCalculatePrem"):

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCalculatePrem_Click()
  2.  
  3. Dim dteEffectiveDate As Date
  4. Dim rate, rate2, rate 3 As Variant
  5.  
  6. dteEffectiveDate = CDate(Me.[Effective Date])
  7.  
  8. 'Get the rates for the three levels...
  9.  
  10. rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 1"))
  11.  
  12. rate2 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 2"))
  13.  
  14. rate3 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 3"))
  15.  
  16. 'Now calculate the three respective premiums...
  17.  
  18. Me.[Umb Prem 1] = rate1*[U/L Prem]*[U/L Mod]*[Umb Mod]
  19. Me.[Umb Prem 2] = rate2*[U/L Prem]*[U/L Mod]*[Umb Mod]
  20. Me.[Umb Prem 3] = rate3*[U/L Prem]*[U/L Mod]*[Umb Mod]
  21.  
  22. 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
Mar 3 '10 #16
patjones
931 Expert 512MB
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

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 3 '10 #17
LBinGA
14
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 cmdCalculatePrem'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 dteEffectiveDate 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
Mar 18 '10 #18
patjones
931 Expert 512MB
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 dteEffectiveDate 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 dteEffectiveDate 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
Mar 18 '10 #19
LBinGA
14
Ok, Pat, I think I see. I did F8.
The first line it breaks on is:
rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 1"))

I can't get it to move past that.

Also, my question on the CDate line
dteEffectiveDate = CDate(Me.[Effective Date])
was why use [Effective Date] here and not [dteEffectiveDate]? I renamed the field to [dteEffectiveDate] 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
Mar 25 '10 #20
patjones
931 Expert 512MB
Ok, Pat, I think I see. I did F8.
The first line it breaks on is:
rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDate & "# AND [fldDateUpper] >= #" & dteEffectiveDate & "# AND [fldLevel] = 1"))
This indicates that there is a mismatch in type somewhere in the criteria argument. On this line, try replacing dteEffectiveDate with the value that is in the text box directly: Me.[Effective Date].Value.

This would basically remove the need to calculate dteEffectiveDate = 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
Mar 25 '10 #21
LBinGA
14
Hi Pat:
Ok, I'm sorry I'm not explaining it properly. The formula would be exactly how you've written it:
Expand|Select|Wrap|Line Numbers
  1. A*B=C*D=E*F=G
  2.  
  3.      A    *     B    =     C   *  D   =    E   *    F    =     G
  4. U/L Prem1 * U/L Mod1 = U/L Man1*rate1 = Man XS1*Umb Mod1 = Umb Prem 1
  5. U/L Prem2 * U/L Mod2 = U/L Man2*rate2 = Man XS2*Umb Mod2 = Umb Prem 2
  6. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. '*******************Prem Ops Mod**********************************
  3.     If Me.Prem_Ops_U_L_Mod_1 = 0 Then
  4.         [Prem/Ops U/L Manual 1] = [Prem/Ops U/L Prem 1]
  5.     Else
  6.         [Prem/Ops U/L Manual 1] = ([Prem/Ops U/L Prem 1] / [Prem/Ops U/L Mod 1])
  7.     End If
  8.  
  9.     If Me.Prem_Ops_U_L_Mod_2 = 0 Then
  10.         [Prem/Ops U/L Manual 2] = [Prem/Ops U/L Prem 2]
  11.     Else
  12.         [Prem/Ops U/L Manual 2] = ([Prem/Ops U/L Prem 2] / [Prem/Ops U/L Mod 2])
  13.     End If
  14.  
  15.     If Me.Prem_Ops_U_L_Mod_3 = 0 Then
  16.         [Prem/Ops U/L Manual 3] = [Prem/Ops U/L Prem 3]
  17.     Else
  18.         [Prem/Ops U/L Manual 3] = ([Prem/Ops U/L Prem 3] / [Prem/Ops U/L Mod 3])
  19.     End If
  20.     '*******************Prem Ops Mod 1 **********************************
  21. Dim Effective_Date As Date
  22. If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
  23.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
  24.     [Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]
  25.  
  26. ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
  27.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.10)
  28.     [Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]
  29.  
  30. ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
  31.     [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.11)
  32.     [Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]
  33.  
  34. End If
  35.  
  36.  
  37. '*******************Prem Ops Mod 2**********************************
  38.  
  39. If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
  40.     [Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.13)
  41.     [Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]
  42.  
  43. ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
  44.     [Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.14)
  45.     [Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]
  46.  
  47. ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
  48.      [Prem/Ops Manual XS 2] = ([Prem/Ops U/L Manual 2] * 0.15)
  49.     [Prem/Ops Prem 2] = [Prem/Ops Manual XS 2] * [Prem/Ops Mod 2]
  50.  
  51. End If
  52.  
  53. '*******************Prem Ops Mod 3 **********************************
  54.  
  55. If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
  56.     [Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.2)
  57.     [Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]
  58.  
  59. ElseIf Me.Effective_Date >= #1/1/2011# And Me.Effective_Date <= #12/31/2011# Then
  60.     [Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.21)
  61.     [Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]
  62.  
  63. ElseIf Me.Effective_Date >= #1/1/2012# And Me.Effective_Date <= #12/31/2012# Then
  64.     [Prem/Ops Manual XS 3] = ([Prem/Ops U/L Manual 3] * 0.22)
  65.     [Prem/Ops Prem 3] = [Prem/Ops Manual XS 3] * [Prem/Ops Mod 3]
  66.  
  67. End If
  68.  
  69. '*************************************Prods*************************
  70.  
  71. If [Prods U/L Mod A] = 0 Then
  72.     [Prods U/L Manual A] = [Prods U/L Prem A]
  73. Else
  74.     [Prods U/L Manual A] = ([Prods U/L Prem A] / [Prods U/L Mod A])
  75.  
  76. End If
  77.  
  78. If [Prods U/L Mod B] = 0 Then
  79.     [Prods U/L Manual B] = [Prods U/L Prem B]
  80. Else
  81.     [Prods U/L Manual B] = ([Prods U/L Prem B] / [Prods U/L Mod B])
  82.  
  83. End If
  84.  
  85. If [Prods U/L Mod C] = 0 Then
  86.     [Prods U/L Manual C] = [Prods U/L Prem C]
  87. Else
  88.     [Prods U/L Manual C] = ([Prods U/L Prem C] / [Prods U/L Mod C])
  89.  
  90. End If
  91.  
  92.  
  93. '*******************Prods A*****************************************
  94.  
  95. If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
  96. [Prods Manual XS A] = ([Prods U/L Manual A] * 0.1)
  97. [Prods Prem A] = [Prods Manual XS A] * [Prods Mod A]
  98. End If
  99.  
  100. '*******************Prods B*****************************************
  101. If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
  102. [Prods Manual XS B] = ([Prods U/L Manual B] * 0.15)
  103. [Prods Prem B] = [Prods Manual XS B] * [Prods Mod B]
  104. End If
  105. '*******************Prods C*****************************************
  106. If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
  107. [Prods Manual XS C] = ([Prods U/L Manual C] * 0.22)
  108. [Prods Prem C] = [Prods Manual XS C] * [Prods Mod C]
  109. End If
  110.  
  111. '*************************************Auto*************************
  112.  
  113. If [AL U/L Mod] = 0 Then
  114.     [AL Manual] = [AL U/L Prem]
  115. Else
  116.     [AL U/L Man] = ([AL U/L Prem] / [AL U/L Mod])
  117.  
  118. End If
  119.  
  120. '*******************Prods A*****************************************
  121.  
  122. If Me.Effective_Date >= #1/1/2010# And Me.Effective_Date <= #12/31/2010# Then
  123. [AL Man XS] = ([AL U/L Man] * 0.1)
  124. [AL Prem] = [AL Man XS] * [AL Mod]
  125. End If
  126. End Sub
God, I hope that makes sense to you....my head is swimming!

Thank you,
LB
Mar 25 '10 #22
patjones
931 Expert 512MB
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
Mar 25 '10 #23
patjones
931 Expert 512MB
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

Expand|Select|Wrap|Line Numbers
  1. [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
  2. [Prem/Ops Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Mod 1]
  3.  

could just be written

Expand|Select|Wrap|Line Numbers
  1. [Prem/Ops Prem 1] = 0.09 * [Prem/Ops U/L Manual 1] * [Prem/Ops Mod 1]
  2.  

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

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

Pat
Mar 25 '10 #24
LBinGA
14
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..... ?
Mar 26 '10 #25
patjones
931 Expert 512MB
OK, so how about this (it will calculate all three rate levels for layer 1)...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCalculatePrem_Click()
  2.  
  3. Dim rate, rate2, rate 3 As Variant
  4.  
  5. 'Get the rates for the three levels...
  6.  
  7. rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & Me.[Effective Date] & "# AND [fldDateUpper] >= #" & Me.[Effective Date]& "# AND [fldLevel] = 1"))
  8. rate2 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & Me.[Effective Date] & "# AND [fldDateUpper] >= #" & Me.[Effective Date] & "# AND [fldLevel] = 2"))
  9. rate3 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & Me.[Effective Date] & "# AND [fldDateUpper] >= #" & Me.[Effective Date] & "# AND [fldLevel] = 3"))
  10.  
  11. 'Now calculate the three rate levels for layer 1...
  12.  
  13. Me.[Umb Prem 1_1] = ( Me.[U/L Prem 1] / Me.[U/L Mod 1] ) * rate 1 * Me.[Umb Mod 1]
  14. Me.[Umb Prem 1_2] = ( Me.[U/L Prem 1] / Me.[U/L Mod 1] ) * rate 2 * Me.[Umb Mod 1]
  15. Me.[Umb Prem 1_3] = ( Me.[U/L Prem 1] / Me.[U/L Mod 1] ) * rate 3 * Me.[Umb Mod 1]
  16.  
  17. 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
Mar 26 '10 #26

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Thomas R. Hummel | last post by:
Hello, I am importing data that lists rates for particular coverages for a particular period of time. Unfortunately, the data source isn't very clean. I've come up with some rules that I think...
12
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date...
10
by: MLH | last post by:
Suppose the following... Dim A as Date A=#7/24/2005# I wish to compare value of A against 2 other values: 1) 8/1/2005 2) 9/1/2005 Which is better and why... First:
1
by: Bob Day | last post by:
Why do the if/then and Select Case statements below results in different outcomes? Bob Day Dim Test_Object As Object = 0 If Not (Test_Object Is Nothing) Then ' this considers Test_Object...
67
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ...
2
by: scole954387 | last post by:
Hi, I have a problem. I have written a SQL statement that has a nested select case statement on the 'where' clause to condition the results. ...
1
by: cryptotech2000 | last post by:
I am trying to filter this query based on date ranges but it don't seem to be working correctly, its probably something really simple if anyone can help me with this select...
21
beacon
by: beacon | last post by:
Hello to everybody, I have a section on a form that has 10 questions, numbered 1-10, with 3 option buttons per question. Each of the option buttons have the same response (Yes, No, Don't know),...
4
by: Patrick A | last post by:
All, I rely on nested IF statements with multiple conditions heavily, and someone suggested recently writing the statements (and especially reading them months later) would be much easier if I...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.