Nested IIF Statement (and Select Case) with Date Ranges

[LBinGA]

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:

Dim Effective_Date As Date
If Me.Effective_Date >= 1/1/10 And Me.Effective_Date <= 12/31/10 Then
    [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
    [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]

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

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

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_Da te="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.

Dim Effective_Date As Date
Select Case [Effective Date]
Case Is >= 1 / 1 / 10, Is <= 12 / 31 / 10
    [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09)
    [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]

Case Is >= 1 / 1 / 11, Is <= 12 / 31 / 11
    [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.22)
    [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS 1] * [Prem/Ops Umb Mod 1]
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

[patjones]

Hi LBinGA -

Just from a quick look, I would ask you to try enclosing the dates in "#" signs. For instance,

...
If Me.Effective_Date >= #1/1/10# And Me.Effective_Date <= #12/31/10# Then
...

might work. Let me know whether this works out for you.

Pat

[LBinGA]

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

[TheSmileyCoder]

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.

[patjones]

I'm glad it worked out for you...

I personally consider "Case...Sel ect" 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

[LBinGA]

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

[patjones]

Hi -

First, I would put

[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

Dim dteEffectiveDate As Date
Dim rate As Variant

rate = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] < #" & dteEffectiveDate & "# AND [fldDateUpper] > #" & dteEffectiveDate & "#"))

[Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] *rate)
...

The third argument in DLookup is a concatenated string, because if you put dteEffectiveDat e 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

[patjones]

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

[LBinGA]

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

[LBinGA]

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

[patjones]

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...

...
Dim intLevel As Byte

intLevel = ? ? ?

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

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

[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

[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

[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

[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