IIF function with variables

[coastal]

I have column A with about 10 different categories, and I want to create
column B that is populated based on the entries in column A. If for row 1,
Column A has categotry 1 through 5 then column B should read "web based", if
it has 6 through 10 then it should be left blank. How do I adjust my Iif
function to account for a variable in the "if true criteria" portion of the
function?

Expr1: IIf([soc]="Variable","web")

 

[Jeff Boyce]

One approach, rather than trying to build this dynamically within a query,
might be to create a "lookup" table that stores the valid value pairs.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michel Walsh]

Indeed, as example:

Ranges ' table name
Low High Label ' fields name
-------------------------------------------------
1 5 "web based"
6 10 " "
11 9999 "something unexpectedly high"
-9999 0 "something unexpectedly low"

... data sample


and then:


SELECT myTable.field1, myTable.field2, ranges.label
FROM myTable INNER JOIN ranges
ON (myTable.field1 BETWEEN ranges.Low AND ranges.High)



would do.


Furthermore, since the 'ranges' are defined in a table (rather than in your
code), it is much easier for anyone using your application to make a change,
to define new ranges, etc., it that becomes require, in the future.



Hoping it may help,
Vanderghast, Access MVP

One approach, rather than trying to build this dynamically within a query,
might be to create a "lookup" table that stores the valid value pairs.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have column A with about 10 different categories, and I want to create
column B that is populated based on the entries in column A. If for row
1,
Column A has categotry 1 through 5 then column B should read "web based",
if
it has 6 through 10 then it should be left blank. How do I adjust my Iif
function to account for a variable in the "if true criteria" portion of
the
function?

Expr1: IIf([soc]="Variable","web")