Where to type the IIF statement?

[Guest]

How do I use IIF to do the following...

I have a query with a column header (field) titled "SCORE"
The values or (records) for score are "HIGH" or "LOW"

I want to include the "SCORE" field or column and...
Add a new column that converts the HIGH or LOW to a numerical value of "3"
or "1"

I tried using the following expression:

IIF([SCORE]="HIGH",1,IIF([SCORE]="LOW",1," "))

However, I got errors. I really don't know where this expression should by
typed into....do I type it into the "Field" row or the "Criteria" row of the
query design view?

Thanks!

 

[Pat Hartman]

Type the expression in the Field row:

ScoreNum:IIF([SCORE]="HIGH",3,IIF([SCORE]="LOW",1,Null))
or if you want the value for not high and not low to be 2 --
ScoreNum:IIF([SCORE]="HIGH",3,IIF([SCORE]="LOW",1,2))

You had both high and low returning 1, so I changed high to return 3.

 

[Guest]

Pat,
Thanks for your suggestion, I think we're closer, but not quite there yet.

Access seemed to take the expression you suggested below. However, when I
returned to the query view (not the design view), each record under the new
column "ScoreNum" contained "#Error" in it.

Any ideas?

Type the expression in the Field row:

ScoreNum:IIF([SCORE]="HIGH",3,IIF([SCORE]="LOW",1,Null))
or if you want the value for not high and not low to be 2 --
ScoreNum:IIF([SCORE]="HIGH",3,IIF([SCORE]="LOW",1,2))

You had both high and low returning 1, so I changed high to return 3.

How do I use IIF to do the following...

I have a query with a column header (field) titled "SCORE"
The values or (records) for score are "HIGH" or "LOW"

I want to include the "SCORE" field or column and...
Add a new column that converts the HIGH or LOW to a numerical value of "3"
or "1"

I tried using the following expression:

IIF([SCORE]="HIGH",1,IIF([SCORE]="LOW",1," "))

However, I got errors. I really don't know where this expression should by
typed into....do I type it into the "Field" row or the "Criteria" row of the
query design view?

Thanks!

 

[Steve Schapel]

Help Me,

I think the expressions which Pat suggested are valid, so I can't think
of a reason for it not working, unless you made a typo in the expression
when you entered it in query design.

Here's an alternative, which I would personally prefer...
ScoreNum:Switch([SCORE]="HIGH",3,[SCORE]="LOW",1)

 

[Guest]

OK...Thanks Steve, it seemed to work. However there's a new catch that I'm
trying to resolve.

The "expression" works only if the records in the orginal "Score"
column/field are "text" or "hand-typed". For example, someone actually typed
in "HIGH", "MEDIUM", or "LOW".

The expression does not work if the records in the "Score" column are picked
from a "pull-down" list or combo box. In this case, the result I get is a
bunch of empty boxes in the new field "ScoreNum".

So, it seems like the expression only recognizes hand-typed text, but not
combo box.

Any thoughts?

Help Me,

I think the expressions which Pat suggested are valid, so I can't think
of a reason for it not working, unless you made a typo in the expression
when you entered it in query design.

Here's an alternative, which I would personally prefer...
ScoreNum:Switch([SCORE]="HIGH",3,[SCORE]="LOW",1)

--
Steve Schapel, Microsoft Access MVP

Pat,
Thanks for your suggestion, I think we're closer, but not quite there yet.

Access seemed to take the expression you suggested below. However, when I
returned to the query view (not the design view), each record under the new
column "ScoreNum" contained "#Error" in it.

Any ideas?

 

[Steve Schapel]

Help Me,

It sounds like something odd about the combobox. Like it's a
multi-column combobox, with one column hidden, and it is the hidden
ciolumn which is the "bound column", so even though it appears that the
value being entered int he field is "HIGH" etc, this is not the actual
data being entered, it is the value of the hidden combobox column. This
sort of confusion sometimes arises as a result of using the Lookup
Wizard to create the field in table design.