Multiple IIf

[PaulJK]

I am struggling with an expression I am trying to bulid in a query.

There is a band of answers I would like to be entered depending on the value in another box. The current expression I have is:

Systolic: IIf([FABloodPressure]<130,"Normal",[FABloodPressure]Between 130 And 139,"High Normal",[FABloodPressure]Between 140 And 149,"Mild Hypertension",[FABloodPressure]Between 150 And 159,"Moderate Hypertension", "Super Hypertension")

I have tried to remove subsequent FABloodPressure items and the IIF statements but cannot get anything to work.

If someone can point me in the right direction, I would appreciate it.

Many thanks

 

[Newman]

That is how multiple IIf works:
IIf(1stCondition,"True",IIf(2ndCondition,"True",IIf(3rdCondition,"True",IIf(4thCondition,"True","False"))))
You could also use an IIf statement instead of a "True".
Just remember that a IIf statement always have three parts:
IIf(Condition, True, False)
The trick is to replace True or False by another IIf statement.

 

[Elmer Homero]

Try this:

IIf([Table1]![FABloodPressure]>159,"Super Hypertension",IIf([Table1]![FABloodPressure]>149,"Moderate Hypertension",IIf([Table1]![FABloodPressure]>139,"Mild Hypertension",IIf([Table1]![FABloodPressure]>129,"High Normal","Normal"))))

Table1 is the name of the table where the FABloodPressure field is in.

 

[emcf]

what is the limit to the number of Iif statements within a query? ii tried to use one that has 27 and access complains and says it is too complicated........is there a way around this?

i tried another method but again it did not work...
iif([field1] = 1 or 2 or 5, "result1", IIf([Field1]= 3 or 4 or 6, "result2", etc. etc. etc.

it only returns the value result1 no matter what field1 actually equals!

 

[Newman]

Maybe these could help...
I don't know the english name of that function, but, if translate word to word, it would be "FirstTrue".
It is like a "Select Case" in Basic or a "Switch" in C++.
That is all I can tell...

 

[Newman]

I think that you should say:
iif([field1] = 1 or [field1] = 2 or [field1] = 5
instead of:
iif([field1] = 1 or 2 or 5

 

[raskew]

Paul-
Try the Switch() function. It's a lot less messy than nested Iif()s. In an example, I created a calculated field (x) to show how many degrees? the BP is above the baseline of 130. Then it's just a matter of categorizing x:

Switch([x]=0,"Normal",[x]<10,"High Normal",[x]<20,"Mild Hypertension",
[x]<30,"Moderate Hypertension",True,"Super Hypertension") AS y

Here's the actual query used:

SELECT tblBP.name, tblBP.FABloodPressure, 
IIf([FABloodPressure]\130=0,0,[FABloodPressure] Mod 130) AS x, 
Switch([x]=0,"Normal",[x]<10,"High Normal",[x]<20,"Mild Hypertension",
[x]<30,"Moderate Hypertension",True,"Super Hypertension") AS y
FROM tblBP;

 

[raskew]

emcf, Newman-

Use the Switch() function combined with either the Instr() or IN() function. Here's an example that shows both:

SELECT tblBP.name, tblBP.Field1, 
Switch(InStr("1,3,5",[field1])>0,"Result 1",InStr("2, 4, 6",[field1])>0,"Result 2") AS x, 
Switch([Field1] In (1,3,5),"Result 1",[field1] In (2,4,6),"Result 2") AS Expr1
FROM tblBP;

 

[emcf]

cheers folks,

Iif([field1]=1 or [field1]=2 etc

works absolutely perfectly!