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

 

[jdegeorge]

You don't want IF. You want CASE.

Select Case [FLD3]
Case < 49
whatever you want done
Case >50 and <59
etc...
Case >60 and <69
etc...
Case >=70

End Select

Hope this helps

Jim DeGeorge

 

[jdegeorge]

Left this out....


You would put this code behind FLD3's AfterUpdate property.

Jim DeGeorge

 

[CajunCenturion]

There are several issues that you need to address:

First, lets look at the IIF function. It needs three parts 1-condition, 2-true expression and 3-false expression
Looking at just your first IIf:

iif([FLD3]<49,int(6+1)*rnd+1),

you have a condition: [FLD3]<49
a true expression: int(6+1)*rnt+1
but no false expression. The close paren matches up with the IIf open. That mistake is repeated throughout all of the IIf statement, with the last IIF not having a false expression.

Second, the calculated amount to add is opened with a square bracket, but is not closed. Further, I don't think square brackets are correct, I think parens are the better choice.

Finally, looking at your use of the Rnd function. The general form is Int((Upper - Lower + 1) * Rnd + Lower)
Your first rnd:

  int(6 + 1) * rnd + 1

and should be:

  int((6 - 1 * rnd + 1)

and again, that same error is applied throughout.

Check through these comments, and rework your statement, and if you're still having troubles, post a follow-up with your latest version of the expression.

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

 

[CajunCenturion]

One more thing I just noticed. Your range does not take into account the specific values for 50, 60 and 70. Also keep in mind that your last check (>69) doesn't need it's own IIf as it could be the final false condition.

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

 

[tstowe]

Thanks for everyone's quick reply. I will sort through your information and see which works best. Again, Thanks

Tony

 

[tstowe]

jdegeorge,

Your suggestion seems most practically to me but I am still have trouble. I continue to get a compile error message.

Here is what I have entered:

Private Sub Text87_AfterUpdate()
Select Case [TimeInCbt]

Case Is < 49
([timeincbt]/12)+[education]+8+(int((6 - 1) * rnd + 1))

Case >50 and <59
[timeincbt]/12)+[education]+8+(int((12 - 1) * rnd + 1))

Case >60 and <69
[timeincbt]/12)+[education]+8+(int((18 - 1) * rnd + 1))

Case Is >= 70
[timeincbt]/12)+[education]+8+(int((24 - 1) * rnd + 1))
End Select
End Sub

Any additional corrections??

Thanks

Tony

 

[Edski]

1. You need to assign something to the expressions.
Answer = ([timeincbt]/12)+[education]+8+(int((6 - 1) * rnd + 1))

2. You are still missing the 50 and 60.

 

[Edski]

Actually you are missing 49, 50, 59, 60 AND 69 !!!

 

[sfm6s524]

I'd be tempted to throw this calculation into a function. That way you can use one line of code to set the value. And if the formula changes, you'll only need to change it in one location. Create new module, paste everything between &quot;Function...End Function&quot; from below into module and save.

Function WhateverAmount(Education As Variant, TimeInCBT As Variant) As Long

Dim RetVal As Long

RetVal = Int((Nz(TimeInCBT) / 12) + Nz(Education) + 8)
Select Case Nz(TimeInCBT)
Case Is < 50
RetVal = RetVal + Int((6 - 1) * Rnd + 1)
Case Is >= 50 < 60
RetVal = RetVal + Int((12 + 2) * Rnd + 1)
Case Is >= 60 < 70
RetVal = RetVal + Int((18 + 3) * Rnd + 1)
Case Is >= 70
RetVal = RetVal + Int((24 + 4) * Rnd + 1)
End Select
WhateverAmount = RetVal

End Function

Then on your form, on the AfterUpdate events of both the TimeInCBT and Education fields, put the code Me!txtDestField = WhateverAmount(Me!txtTimeInCBT, Me!txtEducation). The Me!txtDestField would be the textbox that you are putting the resulting calculation in.

 

[Edski]

Whenever I see a case statement I try to create a simple function to eliminate the number of lines of code. There's an obvious pattern here and a fairly straightforward function could do the trick.

Given any real positive number, this function needs only to determine one of four integers: 1, 2, 3 or 4.

Using the original variables instead of the ones sfm6s524 gave (but using his nice vb function) you could get away with this:
-------------------------------------------
Function WhateverAmount(Fld3 As Double, Fld2 As Double) As Long

Dim RetVal As Long
Dim n As long [green]' Lower[/green]
Dim m As Long [green]' Upper[/green]

RetVal = Int(Fld3 / 12) + Fld2 + 8

n = (Int(Fld3 / 10) - 4) * (Fld3 >= 50) + 1 [green]' returns 1 to oo[/green]
If n > 4 Then n = 4 [green]' upper cut-off[/green]
m = 6 * n

WhateverAmount = RetVal + Int((m - n) * Rnd + 1)

End Function
-------------------------------------------
I know, I know - same diff. But if there were 10 Case statements instead of just 4 then it would be a big reduction in the number of lines used. Also you wouldn't have to add or change much. Kinda like Normalising your code. You could even go further by replacing the numeric values for constants, or even pass the cut-off values through the function as well!

 

[Edski]

Oops, I forgot that Access treats the Boolean True as -1.
So just square the Boolean part of the line:

n = (Int(Fld3 / 10) - 4) * (Fld3 >= 50) ^ 2 + 1 [green]' returns 1 to oo[/green]

: )

 

[tstowe]

First let me thank you all for helping me with this. The coding is kicking my ass but your help is opening the doors for me and for that I am thankful.

Edski - I really didn't understand how your code fit in, or replaced, sfm6s524's code so I will leave the discussion open to you to explain it further for me - thanks. I simply could not see the connection.

My understanding of what you guys are offering is straining to me as I am very weak on the whole coding issue. With that said, I took sfm6s524's code an put it into a module and saved it as MOD1 (is there a preferred manner of saving Modules??) I then input the code from Function Whatever. . . to End Function into the module. I then went into each Afterupdate on both timeincbt and education and entered in the following code:

Me!txtAge=Whateveramount(me!txttimeincbt, me!txteducation)

With that complete, the field that I want the calculation performed in, unbound field named AGE, remains empty. I am not sure if the module automatically takes affect or if I am suppose to connect the module to my form in another manner.

Question 1: In the above code that I entered in the Afterupdate of both timeincbt and education - Am I suppose to write it as such:

Me!txtage=whateveramount(me!txttimeincbt) in the timeincbt

AND separately

me!txtage=whateveramount(me!txteducation) in education

Or is it supposed to remain as you indicated in the same line?

Again, thanks for you help.

Tony

 

[CajunCenturion]

I think that your first instinct to use the compound IIf statement as the control source was the better approach than writing a function. It will be faster, and incur less overhead. I think the following would be the correct statement.

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

Breaking it down into its component parts you get the following:

Simple calculation     :  =([FLD3]/12)+[FLD2]+8+
Begin Complex IIF      :  (
First IIF Condition    :    IIf(
1st IIF Condition      :         ([FLD3]<49),
First IIF True Part    :         Int(6*Rnd+1),   
1st False Part(2nd IIF):         IIf(
2nd IIF Condition      :              ([FLD3]>=50 And [FLD3]<59),
2nd IIF True Part      :              Int(11*Rnd+2),
2nd False Part 3rd IIF :              IIf(
3rd Condition          :                    ([FLD3]>=60 And [FLD3]<69),
3rd True Part          :                    Int(16*Rnd+3),
3rd False Part         :                    Int(21*Rnd+4)
Close 3rd IIF          :                  )
Close 2nd IIF          :            )
Close 1st IIF          :        )
Close Complex IIF      :  )

Now look at the four round statement. Remember, the formula when using Rnd is
((Upper - Lower + 1) * Rnd + Lower)

Int(6*Rnd+1) : (Upper =  6, Lower = 1 ==>  6 - 1 + 1  ==>  6*Rnd+1
Int(11*Rnd+2): (Upper = 12, Lower = 2 ==> 12 - 2 + 1  ==> 11*Rnd+2
Int(16*Rnd+3): (Upper = 18, Lower = 3 ==> 18 - 3 + 1  ==> 16*Rnd+3
Int(21*Rnd+4): (Upper = 24, Lower = 4 ==> 24 - 4 + 1  ==> 21*Rnd+4

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

 

[CajunCenturion]

And even after the preview, I missed a couple of equal signs

2nd IIF Condition      :              ([FLD3]>=50 And [FLD3]<=59),
3rd Condition          :                    ([FLD3]>=60 And [FLD3]<=69),

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

 

[jdegeorge]

Nothing happens in this code when the value is 59, 69, or if 70 or greater.

Jim DeGeorge

 

[CajunCenturion]

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.

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

 

[jdegeorge]

I see it... Int(21*Rnd+4).

Enjoy a star from me too!


Jim DeGeorge

 

[tstowe]

CajunCenturian,

Your post worked without a hitch. Thanks for the simplified code and the explanation. I tried to do just what you posted but I had left out alot of extras - which in turn caused an error.

Thank you all.

sfm6s524 and Edski: I am still interested in knowing how to make your code work. Thanks

Tony

 

[CajunCenturion]

Please be sure that you have the conditionals correct
<= 49
<= 59
<= 69

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

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