Multi IIF statements 3

[tstowe]

Here is the equation:

Fld1: 8
Fld2: 12
Fld3: 15

I need my calculating field to do two things.

FIRST, the simple part:

=([Fld3]/12)+[Fld2]+8

SECOND, where I am confused:

I need to use an IIF statement to look at Fld3 and do the following:

If FLD3 is <49 then add 1-6 points to the above
If FLD3 is >50 but <59 then add 2-12 points to above
If FLD3 is >60 but <69 then add 3-18 points to above
If FLD3 is >70 then add 4-24 points to above

My attempt at this is as follows:

=([FLD3]/12)+[FLD2]+8+[iif([FLD3]<49,int(6+1)*rnd+1),iif([FLD3]<59,int(12+2)*rnd+1),iif([FLD3]<69,int(18+3)*rnd+1),iif([FLD3]>69,int(24+4)*rnd+1)

Any suggestions as to where I am screwing this up???

Thanks

Tony

 

[tstowe]

You wrote, &quot;I did miss the <=59 and the <=69 as I already admitted in my most recent post, but anything 70 or greater will be caught be the False Part of the 3rd IIF statement.&quot;

Does this mean that I may encounter an error at some point? If so, how do I correct this and if not. . .the code is working thus far.

Tony

 

[CajunCenturion]

No, that was answering jdegeorge comments. We were probably both posting as the same time.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tstowe]

I asked too quickly. Thanks.

Tony

 

[jdegeorge]

Correctamundo, Cajun! Seems that our updates don't get posted quick enough and sometimes our comments overlap.

Tony...Cajun's got your back on this one! Nice code. Gotta love those nested IIFs!

Jim DeGeorge

 

[CajunCenturion]

My preference however, Jim, would be to use the Switch function, but didn't want to confuse the issue .... yet.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jdegeorge]

Ah! &quot;Yet&quot; means that you're planning on posting the &quot;switch&quot; version of this code in the future! Sneaky! ;-)

Jim DeGeorge

 

[CajunCenturion]

Well, I figured that until a working solution was in place, it would simply muddy the waters, but here is the switch equivalent:

=([FLD3]/12)+[FLD2]+8+Switch(([FLD3]<=49),Int(6*Rnd+1),([FLD3]>=50 And [FLD3]<=59),Int(11*Rnd+2),([FLD3]>=60 And [FLD3]<=69),([FLD3]>=70),Int(21*Rnd+4))

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tstowe]

Now that the discussion has graduated (I suppose) to the Switch function. . .how does this improve the code?

Tony

 

[CajunCenturion]

Improvement in the code, at least IMHO, would only be achieved from either a performance standpoint, or a code readability/maintainability standpoint, or both.

The performance issue could be settled by running some timing tests to see which is faster, the nested IIf's or the Switch function. For fair comparision, I would also include the Case Statement option, and Edski's approach which takes good advantage of the numerical relationships between the values being used. You would have to factor into the tests the distribution across the IIf's to determine how deep you get until a True clause is executed. What I mean by that is that you can optimize your code so that the most likely condition is checked first, with the least likely occurrence being the deepest IIf. In your case, if 70% of the [FLD3] values lie between 60 and 69 (inclusive), then you would want to check that range first, so that 70% of the time, you never have to go more than one level deep in the IIf's. I don't know for sure, but I suspect that the Switch and Case approaches will perform rather consistently across all distributions. You would also in the timing tests, need to factor in whether or not you're calling intrinsic functions within the control source, like the Switch function, or you're calling User-Defined functions (UDF) as would be required for the Case statement or Edski's approach. You also need to insure that your test results are not skewed by some other process running on the machine during the tests. Needless to say, to do accurate testing is not as straightforward as it might appear.

From a code readability and/or maintainability, it probably boils down to a personal preference and subjective judgments, but I lean towards the Switch as being easier to read and maintain than the nested IIf as far as completely embedded Control Source statements. However, a UDF with either the Switch function or Case statement would be easier to deal with than an embedded nested IIf.

I would not be surprised at all if Edski's approach had the best performance (especially if you can eliminate the exponentiation), but would probably be the least intuitive code from a readability standpoint. Come back one year later, and the Switch and Cases would easy to decipher, but Edski's code might not come back to you as quickly. Nevertheless, that's a fair tradeoff for performance.

So to answer your question: &quot;how does this improve the code&quot;, the best answer I can give is: &quot;It depends&quot;.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tstowe]

Thanks. Another scenario in the same form.

Fld1: 'calculates a base number
Fld2: 'calculates a base number

Each field has two checkboxes, but only ONE checkbox can be selected for each Field. Plus If Fld1Box1 is selected then Fld2Box2 *has* to be selected (although I haven't figured out as to how to make this mandatory yet) to level out the numbers, basically a seesaw requirement - one must happen for the other to be valid (if that makes sense).

Fld1Box1: 'if selected (fld1+20)/2
Fld1Box2: 'if selected fld1/2

Fld2Box1: 'same as above
Fld2Box2: 'same as above

FLD3: has a base calculation of (fld1+fld2)/2

Here is where it gets complicated to me. FLD3 needs recalculate IF any of the checkboxes are selected. But there are several combinations with the calculated number going into FLD3:

1. Calculate as normal, i.e. (fld1+fld2)/2, since
neither checkbox was selected.
2. Fld1Box1 and Fld2Box2 are selected, adjusting the
original Fld1 and Fld2 numbers, FLD3 recalculate
using the adjusted Fld1 and Fld2 fields.
3. Fld1Box2 and Fld2Box1 are selected calculate as
above.

Thanks for your help and I appreciate your time.

Tony

 

[Edski]

tstowe,
I'll answer your first question first.
The function approach first given by sfm6s524 means that you can call the code from anywhere. You wouldn't have to keep writing the same code for each field that needs to call it. In addition, you can pass parameters through the function (your two variables in this case) to obtain the result. However, if you only call this peice of code once, or more precise, from one place, then storing the whole thing in a function procedure may not seem like of much benefit. In this case you may keep it within the Event procedure for that field and be content with that.
The standard way to save a Module is to start it with mod and then finish it with a name that reflects what the module does. For example, I would call sfm6s524's module modRandomiser (or something like that).
The answer to your Question 1 is: Keep it the way sfm6s524 told you.
Now my modification was based on a pattern I saw in the If or Case statements. I replaced the four Case statments with one simple function (a formula in this sense, not the procedure sense). This simply reduces the amount of code you need to write, especially if you had many Case statements.
Don't worry if it doesn't all make sense to you. The more you write programs the more clearer it will all become!
For your most recent problem you will need to write a few lines of code as well. If no one has replied within the next hour or so I will take a look.

 

[Edski]

tstowe, I'd love to know what the three fields represent!

Anyway, first you may want to put each of the two checkboxes inside their own Option Group so that only one checkbox can be selected. They (ChkFld1 and ChkFld2) should both be bound to fields in the table of Data Type = Byte (with the only options being 1 or 2, say).

Then, on the After Update Event for ChkFld1 put:
--------------------------
If ChkFld1 = 1 Then
Fld1 = (Fld1 + 20) / 2
ChkFld2 = 2
ChkFld2.Enabled = False
Else
Fld1 = Fld1 / 2
[green]' Reset ChkFld2 ??? up to you
' if yes, then write ChkFld2 = Null[/green]
ChkFld2.Enabled = True
Endif
Fld3 = (Fld1 + Fld2) / 2
--------------------------

On the After Update Event for ChkFld2 put:
--------------------------
Fld3 = (Fld1 + Fld2) / 2
--------------------------

So now if ChkFld1 is 1 (Option 1 selected) then ChkFld2 should be disabled and set to 2.

If ChkFld1 is 2 (Option 2 selected) then ChkFld2 can be either 1 or 2 or Null, so leave ChkFld2 enabled. (An unfair seesaw!)

You weren't clear if ChkFld2 had the same effect on ChkFld1.

If you want you could add a button(s) that resets the CheckBoxes.
---------------
Sub BT_ResetChkFld1_Click()
CHkFld1 = Null
End Sub
---------------

 

[tstowe]

Edski,

Thanks for your reply. I will try my hand at this later today.

The fields represent a tally sheet (of sorts) that we have on our personnel for assessment. For example, they take tests that are compared against a chart for Fitness and Agility. Man #1 scores 18 and 12 respectfully. Based on the results of other related test for those two specific area's we can either Favor or Slight (for a lack of better terms) those two in an attempt to get a better assessment.

If we Favor Fitness then we have to Slight Agility, so in this case we would take (18+20)/2=19 and then 12/2=6. In this example we would not favor or slight either as the loss to the scores would be too great. But for some scores the results even out more.

The final resulting numbers, from both Fitness and Agility, are then applied to another field that takes the Average of those two scores so that we can compare against others.

 

[janetlyn]

After reading this thread I am not sure now if I should go with IIf or Cases. This is not in the correct format because I typed all this in Word to make sure I had everything. Then when I came here to see how to format it correctly I came upon this thread and am now not sure which direction to go is best. Any suggestions please?

If A1 <> E50 OR A1 <> Q3 Then
A1.Visible = True
E50.Visible = True
Q3.Visible=True
AnnBill.Visible=True
Line176.Visible = True
BoxA1E50Q3.Visible = True
Else If Q5 <> A7 Then
A7.Visible = True
Q5.Visible = True
Line183.Visible = True
AnnCatBill.Visible=True
BoxA7Q5.Visible=True
Else If (E9+E10) <> (E34+E35) OR (E9+E10) <> A4 OR (E9+E10) <> A14 OR (E9+E10) <> A18 OR Then
E9.Visible = True
E10.Visible = True
E34.Visible = True
E35.Visible = True
A4.Visible = True
A14.Visible = True
A18.Visible = True
CumRev.Visible = True
Line189.Visible=True
BoxE9E10E34E35A4A14A18.Visible=True

All help is appreciated, JL

 

[Edski]

janetlyn,
There are a number of issues you may want to consider with your above code.

1. When making controls visible using conditional IF statements, you may want to make them INvisible if the condition is not met.

2. You should add some comments above each conditional expression so that others can understand the code (and you in 6 months time).

3. ElseIf has no space.

4. The third conditional expression is quite long and ends with 'OR Then' which is syntactically incorrect. Or does it just get cut off the page?

Finally what is it you want to achieve? Can you give a brief summary?

 

[janetlyn]

Ed, I worked on this this weekend and decided Case was not what I needed but the If...ElseIf....then. I have since input all the coding. I am having a problem with one of the formulas and have Michael Red and Dan helping me with the problem.

May I ask why I would want to make invisible the things I want to see if they are in error? Appreciate your help, JL

 

[Edski]

I didn't say you would want to. I said you MAY want to.
I have no idea until you give more detailed information on what the code all means.

 

[janetlyn]

Ah. See, the problem is, you smart people who understand code are on a pedestal and your word is &quot;final&quot;. Us idiots, well me, believes everything you say, but sometimes would like to understand the why. If you know a better way, I just want to understand the reasoning behind it. Not that I will understand, but I will try.

Anyhow, this problem, in a different way, is also being discussed in Thread 702-745627. It is an extremely longggggg thread.

Thank you for your help and learning information. JL