Prev: Automotive database template.
Next: Create a linked table
From: John W. Vinson on 1 Apr 2010 12:43
On Thu, 01 Apr 2010 12:31:13 -0400, John Spencer <spencer(a)chpdm.edu> wrote:

>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.

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")

--

John W. Vinson [MVP]
From: Risse on 2 Apr 2010 02:52

"KH" <KH(a)discussions.microsoft.com> kirjoitti
viestiss�: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!
>


First  |  Prev  | 
Pages: 1 2
Prev: Automotive database template.
Next: Create a linked table