IIf return multiple categories

  • Thread starter Thread starter KH
  • Start date Start date
K

KH

Hi,

I want to categorize BMI
<18.5 = "underweight"
18.5-24.9 = "healthy"
25.0-29.9 = "over"
=30 = "obese"
Click to expand...

I am using this IIf statement
BMI_cat: IIf([Pre-BMI]<18.5,"Under",IIf([Pre-BMI] Between 18.5 And
24.9,"Healthy",IIf([Pre-BMI] Between 25 And
29.9,"Over",IIf([Pre-BMI]>="30",[“Obeseâ€]))))

The categories "Under", "Healthy" and "Over" are returned. When I run the
query, I get the error message --> 'Enter Parameter Value' for "Obese", then
the fields that should return "obese" are blank.

Help is greatly appreciated!
 
Why do you have square brackets around "Obese"?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

KH said:
Hi,

I want to categorize BMI
<18.5 = "underweight"
18.5-24.9 = "healthy"
25.0-29.9 = "over"
=30 = "obese"
Click to expand...

I am using this IIf statement
BMI_cat: IIf([Pre-BMI]<18.5,"Under",IIf([Pre-BMI] Between 18.5 And
24.9,"Healthy",IIf([Pre-BMI] Between 25 And
29.9,"Over",IIf([Pre-BMI]>="30",["Obese"]))))

The categories "Under", "Healthy" and "Over" are returned. When I run the
query, I get the error message --> 'Enter Parameter Value' for "Obese",
then
the fields that should return "obese" are blank.

Help is greatly appreciated!
Click to expand...
 
I would never nest IIf() statements like this. The optimum solution is to
have a table of weight ranges with a title field.
At the very least, put all of this logic in a small user-defined function
that accepts the Pre-BMI value and returns a string.


--
Duane Hookom
MS Access MVP


KH said:
Hi,

I want to categorize BMI
<18.5 = "underweight"
18.5-24.9 = "healthy"
25.0-29.9 = "over"
=30 = "obese"
Click to expand...

I am using this IIf statement
BMI_cat: IIf([Pre-BMI]<18.5,"Under",IIf([Pre-BMI] Between 18.5 And
24.9,"Healthy",IIf([Pre-BMI] Between 25 And
29.9,"Over",IIf([Pre-BMI]>="30",[“Obeseâ€]))))

The categories "Under", "Healthy" and "Over" are returned. When I run the
query, I get the error message --> 'Enter Parameter Value' for "Obese",
then
the fields that should return "obese" are blank.

Help is greatly appreciated!
Click to expand...
 
Hi,

I want to categorize BMI
<18.5 = "underweight"
18.5-24.9 = "healthy"
25.0-29.9 = "over"
=30 = "obese"
Click to expand...

I am using this IIf statement
BMI_cat: IIf([Pre-BMI]<18.5,"Under",IIf([Pre-BMI] Between 18.5 And
24.9,"Healthy",IIf([Pre-BMI] Between 25 And
29.9,"Over",IIf([Pre-BMI]>="30",[“Obese”]))))

The categories "Under", "Healthy" and "Over" are returned. When I run the
query, I get the error message --> 'Enter Parameter Value' for "Obese", then
the fields that should return "obese" are blank.

Help is greatly appreciated!
Click to expand...

Remove the square brackets around obese (which indicate a fieldname, not a
text string), and replace the smart quotes “” (which Access won't recognize)
with the plain doublequote character ".
 
Also, you may have to potential for missing some values depending on how the
BMI is calculated and stored.

If BMI could be 24.91 to 24.99 Then you will get no value.
IF BMI could be 29.91 to 29.99 then you will get no value.

I would change your expression to the following.

BMI_cat:
IIf([Pre-BMI] <18.5,"Under"
,IIf([Pre-BMI] <25 ,"Healthy"
,IIf([Pre-BMI] <30,"Over"
,IIf([Pre-BMI] >=30,"Obese","Unknown"))))

That will work since IIF returns the value for the first test that is true.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I want to categorize BMI
<18.5 = "underweight"
18.5-24.9 = "healthy"
25.0-29.9 = "over"
=30 = "obese"
Click to expand...
I am using this IIf statement
BMI_cat: IIf([Pre-BMI]<18.5,"Under",IIf([Pre-BMI] Between 18.5 And
24.9,"Healthy",IIf([Pre-BMI] Between 25 And
29.9,"Over",IIf([Pre-BMI]>="30",[“Obese”]))))

The categories "Under", "Healthy" and "Over" are returned. When I run the
query, I get the error message --> 'Enter Parameter Value' for "Obese", then
the fields that should return "obese" are blank.

Help is greatly appreciated!
Click to expand...

Remove the square brackets around obese (which indicate a fieldname, not a
text string), and replace the smart quotes “” (which Access won't recognize)
with the plain doublequote character ".
Click to expand...
 
Also, you may have to potential for missing some values depending on how the
BMI is calculated and stored.

If BMI could be 24.91 to 24.99 Then you will get no value.
IF BMI could be 29.91 to 29.99 then you will get no value.

I would change your expression to the following.

BMI_cat:
IIf([Pre-BMI] <18.5,"Under"
,IIf([Pre-BMI] <25 ,"Healthy"
,IIf([Pre-BMI] <30,"Over"
,IIf([Pre-BMI] >=30,"Obese","Unknown"))))

That will work since IIF returns the value for the first test that is true.
Click to expand...

The Switch() function is also helpful in these multibranch cases: it takes
pairs of arguments, evaluates them left to right, and when it encounters a
True value in the first of a pair it returns the second of the pair and quits:

BMI_cat: Switch([Pre-BMI] <18.5,"Under"
,[Pre-BMI] <25 ,"Healthy"
,[Pre-BMI] <30,"Over"
,[Pre-BMI] >=30,"Obese"
,True, "Unknown")
 
KH said:
Hi,

I want to categorize BMI
<18.5 = "underweight"
18.5-24.9 = "healthy"
25.0-29.9 = "over"
=30 = "obese"
Click to expand...

I am using this IIf statement
BMI_cat: IIf([Pre-BMI]<18.5,"Under",IIf([Pre-BMI] Between 18.5 And
24.9,"Healthy",IIf([Pre-BMI] Between 25 And
29.9,"Over",IIf([Pre-BMI]>="30",["Obese"]))))

The categories "Under", "Healthy" and "Over" are returned. When I run the
query, I get the error message --> 'Enter Parameter Value' for "Obese",
then
the fields that should return "obese" are blank.

Help is greatly appreciated!
Click to expand...
 
Back
Top