Nested Iif Formula Question

  • Thread starter Thread starter Qaspec
  • Start date Start date
Q

Qaspec

I'm having problems with the following formula when I run a query:

Score:
IIf([Tickets]>=[forms]![MainForm]![Sr1],5,IIf([Tickets]>=[forms]![MainForm]![Sr3],10,IIf([Tickets]>=[forms]![MainForm]![Sr5],20,0)))

I'm trying to rate the tickets count like so -

Anything less than Sr1 = 0
Anything Bewtween SR1 and Sr 2 = 5
Anything Between Sr3 and Sr4 = 10
Anything Greater or equal to SR5 = 20

Please help!
 
Start with top and work down. Why have Sr1, Sr3, and Sr5?

Why not this ---
IIf([Tickets]>=5, 20, IIf([Tickets] Between 3 AND 4, 10, IIf([Tickets] =
2,0)))
 
SR1-5 Refers to controls on a form. I am trying to provide the mangers with
the ability to adjust the scale.

I wasn't sure I could use between in the formula this way. I will try it
thanks!

KARL DEWEY said:
Start with top and work down. Why have Sr1, Sr3, and Sr5?

Why not this ---
IIf([Tickets]>=5, 20, IIf([Tickets] Between 3 AND 4, 10, IIf([Tickets] =
2,0)))

--
Build a little, test a little.


Qaspec said:
I'm having problems with the following formula when I run a query:

Score:
IIf([Tickets]>=[forms]![MainForm]![Sr1],5,IIf([Tickets]>=[forms]![MainForm]![Sr3],10,IIf([Tickets]>=[forms]![MainForm]![Sr5],20,0)))

I'm trying to rate the tickets count like so -

Anything less than Sr1 = 0
Anything Bewtween SR1 and Sr 2 = 5
Anything Between Sr3 and Sr4 = 10
Anything Greater or equal to SR5 = 20

Please help!
Click to expand...
Click to expand...
 
Are both combos to be used as criteria on the same field of the query but at
different times?
If so, then the update event of the combos needs to set the other combo to
null and refresh the query.
The criteria for the field would read this --
[Forms]![Form1]![Combo1] OR [Forms]![Form2]![Combo2]
 
Back
Top