IIf return multiple categories
in [General]
|
From: KH on 31 Mar 2010 19:13 Hi, I want to categorize BMI <18.5 = "underweight" 18.5-24.9 = "healthy" 25.0-29.9 = "over" >=30 = "obese" 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!
From: Jeff Boyce on 31 Mar 2010 19:49 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" <KH(a)discussions.microsoft.com> wrote in message news:EBE70ED9-C1A5-4F89-8E38-D6D248739E22(a)microsoft.com... > Hi, > > I want to categorize BMI > <18.5 = "underweight" > 18.5-24.9 = "healthy" > 25.0-29.9 = "over" >>=30 = "obese" > > 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! >
From: Duane Hookom on 31 Mar 2010 20:19 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" <KH(a)discussions.microsoft.com> wrote in message news:EBE70ED9-C1A5-4F89-8E38-D6D248739E22(a)microsoft.com... > Hi, > > I want to categorize BMI > <18.5 = "underweight" > 18.5-24.9 = "healthy" > 25.0-29.9 = "over" >>=30 = "obese" > > 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! >
From: John W. Vinson on 31 Mar 2010 21:34 On Wed, 31 Mar 2010 16:13:32 -0700, KH <KH(a)discussions.microsoft.com> wrote: >Hi, > >I want to categorize BMI ><18.5 = "underweight" >18.5-24.9 = "healthy" >25.0-29.9 = "over" >>=30 = "obese" > >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! 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 ". -- John W. Vinson [MVP]
From: John Spencer on 1 Apr 2010 12:31
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 John W. Vinson wrote: > On Wed, 31 Mar 2010 16:13:32 -0700, KH <KH(a)discussions.microsoft.com> wrote: > >> Hi, >> >> I want to categorize BMI >> <18.5 = "underweight" >> 18.5-24.9 = "healthy" >> 25.0-29.9 = "over" >>> =30 = "obese" >> 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! > > 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 ". > |