3 conditions in a select query thats populating a newly created fi

[Steve P]

Hi everyone,

I would like to build a query that populates the value of a datafield as
either "HIGH", "Med", or "low" depending on the value of a different numeric
field on the table being queried. I'd like the medium-to-high threshold set
at 10 (lets just say), and the medium-to-low threshold to be set at 5.

How do I build something like that in the expression builder?

I'm actually planning on coding all of this in VBA, so would it be easier to
just have 3 different queries, one for each level distinction?

Please advise.

 

[KARL DEWEY]

Use nested IIF statements.

 

[John Spencer]

You can use a nested IIF statement
IIF([NumberField]>=10, "HIGH", IIF([NumberField] >=5,"MEDIUM",
IIF([NumberField]>=0,"LOW",Null )))

I wouldn't store the value since you would always have to rerun the
calculation when the numberfield data was added or changed. Storing data
that is fully dependent on another field within the record is a bad idea in
almost all cases - it is counter to the rules of good relational database
design.

Just use the expression when you need to show HIGH, MEDIUM, or LOW. You
can use the expression in a query or as the source for a control on a form
or report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..