Using "iif" and "and" statements in a query

[acrocker3434]

I need to create a new field called for BMI percentile. BMI percentile is a combination of age, gender, and BMI. How can I create a field that shows me all of these options? I think it needs to look something liek this:

BMIperc: iif[Age]="18" and [Gender]="male" and [BMI]=18 then = 50, iif[Age]="17" and [Gender]="female" and [BMI]>25 then =95, iif ...and so on

as you can guess there will be a TON of options.

Is this possible to do in a query????

MANY MANY thanks.

 

[ajetrumpet]

BMIperc: iif[Age]="18" and [Gender]="male" and [BMI]=18 then = 50, iif[Age]="17" and [Gender]="female" and [BMI]>25 then =95, iif ...and so on

First of all, you shouldn't use an IIF statement if there are a lot of options here, but rather you should use SWITCH() or a SELECT CASE statement. If you have plenty of value combinations like you are showing above, I would create a function with 3 arguments for AGE, GENDER, and BMI. If you don't have to do this, just do it in a query with an IIF statement. The syntax is this:

iif([Age] = 18 AND [Gender] = "male" AND [BMI] = 18, 50, 
   iif([Age] = 17 AND [Gender] = "female" AND [BMI] > 25, 95, iif(etc, etc...)))

 

[acrocker3434]

can you give me an example

of the SWITCH or SELECT CASE commands? I've never used those before and I'm decent with Access, but very limited with my SQL programming knowledge

there will be hundreds of options to calculate BMI percentile here......I had planned on just writing each one out in the format you noted above, but would LOVE more info on the commands you mentioned to make the process more efficient.

thank you again

 

[Mike375]

http://www.webcheatsheet.com/SQL/access_functions/Switch.php

 

[ajetrumpet]

of the SWITCH or SELECT CASE commands? I've never used those before and I'm decent with Access, but very limited with my SQL programming knowledge

there will be hundreds of options to calculate BMI percentile here......I had planned on just writing each one out in the format you noted above, but would LOVE more info on the commands you mentioned to make the process more efficient.

thank you again

You can't possibly write a hundred or so out. The query would be too complex. the program would tell you so, and it wouldn't event process it.

Isn't there another way you can do this? I would hope you wouldn't have to write out all of the different combinations of values (especially if there are hundreds!). But, if you can't use any kind of input mechanism to retrieve input values, then you have no choice but to list them all out in a module. But, can't you prompt the users for some sort of input here? That would save you a heck of a lot of work....

 

[Mike375]

Since Age and BMI are numbers could their connection or relation to each other be calculated and the IIF or Switch be based on Male or Female.

As Adam has said you will run into problems with what you are proposing.

 

[boblarson]

I would go the function route.

 

[Guus2005]

I would go the function route.

I second that!

 

[ajetrumpet]

I would go the function route.

I third that.

 

[Brianwarnock]

Interested to know what this function does. Does it have a mass of Case Select or Switch functions? Does this mean the DATA is hardcoded in the function?

I'm thinking Excel, 2worksheets, 1 male 1 female, first and easiest split, then a matrix of age and bmi with the cross cell giving the result. I think Index and Match provide that function. Can this approach be replicated in ACCESS?

Brian

 

[JoeyY]

Just Curious

If there are many options, what about putting the combinations in a table and looking the value up? If a combination changed, you would just update the table. Wouldn't this work or is this not advisable and why?

 

[Brianwarnock]

Hi Joey
we do sometimes look for the difficult solutions.
If the data was records in a table then the query form would just input the 3 fields run the query and bingo. sounds good to me.

Brian

 

[ajetrumpet]

If there are many options, what about putting the combinations in a table and looking the value up? If a combination changed, you would just update the table. Wouldn't this work or is this not advisable and why?

This would certainly be the "parameter" method.

Hopefully this can be done, but you still have to know what you're looking for.

I wonder if the person that actually asked this question is ever going to get back in on this discussion...